If you are looking for help loading a variety of geospatial data into PostGIS, then you have landed in the right place. This is a guide for my video below with further tutorials and guides. Special shout out to the team at Crunchy Data for their fantastic post on the topic.
This is a command-line function that comes with PostGIS that allows you to import Shapefiles into a PostGIS database directly.
shp2pgsql -s 4326 -I \ ./Parking_Garages_and_Lots.shp \ > parking_garages_lots.sql # Then run... psql -U user -h localhost \ -d states -f states.sql # Or all in one line shp2pgsql -s 4326 \ -I ./states.shp \ | psql -U user -h localhost states
Another way to do this is to use OGR2OGR, or the vector to vector file translation toolkit from GDAL that allows you to translate one of many different file types into a SQL file and also import it directly to your database.
ogr2ogr \ -f PostgreSQL PG:"host=localhost user=kat password=password \ dbname=states" ./states.geojson \ -nln states -lco GEOMETRY_NAME=geom
You can also use the native COPY command for loading CSV data directly into your database, although you will need to use GDAL and OGR2OGR to turn your data into a CSV if it is not already.
# Turn your files into a CSV ogr2ogr -f CSV file.csv file.shp # Create your new table in the database CREATE TABLE states ( geom GEOMETRY, name TEXT) # COPY the file COPY states FROM './file.csv' (DELIMITER ' ,')
If you are not as familiar with the command line and want a familiar interface I recommend using QGIS with your database. It is super simple and gives you a familiar desktop environment to upload your data.
Finally, you can use GeoPandas to not only read data from PostGIS but also import your GeoDataFrames into a PostGIS database.
from sqlalchemy import create_engine engine = create_engine("postgresql://user:pass@host:5432/db") gdf.to_postgis("my_table", engine)