I have tried performing the query shown below via ogr2ogr and the gpd.GeoDataFrame.from_postgis() method in geopandas.

from osgeo import ogr import psycopg2, os

import geopandas as gpd
import pandas as pd

server = "xxxx.com"
name = "xxxx"
user = "xxxx"
pw = "xxxx"
lyr_name = 'ships'

conn = psycopg2.connect('connection details')
cursor = conn.cursor()
print('executing sql')
query = 'select * from ships a, ships b \
where a.dt_upload>\'2019-01-01\' and b.dt_upload>\'2019-01-01\' \
and a.det_id>b.det_id and ST_DWithin(a.ship_centroid, b.ship_centroid,100)'
#query executed below always fails
gdf = gpd.GeoDataFrame.from_postgis(query,conn, 'ship_center1', crs='EPSG:4326')

ships2 = gdf.iloc[:,7:]
ships2['shipcenter2'] = ships2['shipcenter2'].apply(wkt.loads)
ships2.columns = ships1.columns
new_gdf = gpd.GeoDataFrame(pd.concat([ships1,ships2], ignore_index=True),crs = {'init': 'epsg:4326'} )
new_gdf.rename(columns={'shipcenter1':'geometry'}, inplace=True)
new_gdf.to_file('./ship_coverage.shp' )

Everytime I try the process gets killed. There are about a million rows in my table. How can I optimise this query to fully execute so I can either export the results as a shapefile or as a GeoDataFrame?

  • Can you show the full code? – martinfleis Jun 3 at 21:38
  • 1
    Do you have an index on ship_centroid? What is the SRID of this column? – CL. Jun 4 at 8:16
  • 2
    Your code do not indicate 100m, your code indicate 100 degres ! That correspond to several thousand kilometer ! – obchardon Jun 4 at 10:59
  • 1
    off topic: you may want to change your password now... – JGH Jun 4 at 11:25
  • 2
    @thesunnyscientist, what I would do is to reproject the features to another SRID where units are in meter (using ST_Transform). And, really, if it's not already done, change your password, stackexchange keep an historized version of your question. – obchardon Jun 4 at 12:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.