Plotting a shapefile directly from PostGIS using Python

Question

I'm trying to plot a shapefile directly from PostGIS using Python. My shapefile is already stored in PostgreSQL. I know how to connect Python with PostgreSQL, but I just can't find anything that helps me plot my data into a map. I read somewhere that I should connect to PostgreSQL, query my shapefile table, select the geom attribute, store it into a geodataframe and then plot it.

Here's the code I'm using. Any ideas??

code example:

import psycopg2
import shapely
import osgeo.ogr
import shapely.wkt
import geopandas as gpd
import matplotlib.pyplot as plt
try:
 conn = psycopg2.connect("dbname='strokes' user='postgres' host='localhost' password='****'")
except:
print "I am unable to connect to the database"
cur = conn.cursor()
cur.execute("CREATE INDEX bassin_index ON bassin USING GIST(geom)")
connection.commit()
cur.execute("SELECT st_astext(geom) AS wkt, fid_limite, codebassin FROM bassin")
rows = cur.fetchall()
rows_list=[]
for geom,fid_limite,codebassin in cursor:
 data={'codebassin':codeb,'fidb':fidlim,'geom':shapely.wkt.loads(geom)}
 rows_list.append(data)
 gdf=gpd.GeoDataFrame(rows_list).set_index('codebassin')
 gdf.head()
 gdf.plot(column='rows_list', scheme='QUANTILES', k=5, colormap='gray')
 plt.show()
conn.commit()
conn.close()

• Vince: It's important to realize that "shapefile" is a file format, and once converted into a database, it is no longer a shapefile, but a "table". Similarly, tables are not stored in PostGIS -- PostgreSQL is the database; PostGIS is the database extension that adds geometry and geography datatypes (and their indexes and operators) to PostgreSQL
• MAR: yes i know, thank you for elaborating on that . Therefor do you have any idea on how can I plot my data ?
• Vince: No, but I can tell you that connecting as the postgres user is extremely dangerous to the integrity of your database. Best practice is to create at least two logins, the first to own the data and the second only granted the permissions necessary to utilize the data (for a simple mapping app, SELECT will suffice).
• MAR: Okey i will create another user and give him the necessary permissions. As far as my request is concerned , is it possible or not ? is it doable with python or not ?

Answer 1

First, use method GeoDataFrame.from_postgis() from GeoPandas package to import geodata. Second, apply method GeoDataFrame.plot() to plot your data.

Answer 2

You might be interested to visit: "https://geohackweek.github.io/vector/06-geopandas-advanced/"

Try to use jupyter notebook

• Matt: Welcome to GIS SE. More detailed answers are better... Can you add at least a brief outline from the link?