2

I am trying to build a geocoding app on OSM data, that in regard to arbitrary location will return points within specified radius, as well as distances to the points.

I have the following table:

id      parent_id   key       value         geometry
4821719 221252430   natural   water         POINT (-8.69802 40.60405)
3483776 3335087     leisure   sports_centre POINT (-8.71858 40.61685)
3483777 3335087     leisure   sports_centre POINT (-8.71608 40.61672)

all the geometries are points

I do it with the help of Python geoalchemy2 package with the following expression:

query = session.query(Table,func.ST_Distance_Sphere(Table.geometry, arbitrary_point))\
               .filter(func.ST_Distance_Sphere(Table.geometry, arbitrary_point) < radius)

which translates to sql as:

SELECT table.id, table.parent_id, table.key, table.value, ST_AsEWKB(table.geometry) AS 
geometry, 
ST_Distance_Sphere(table.geometry, ST_GeomFromWKB(:ST_GeomFromWKB_1, :ST_GeomFromWKB_2)) AS 
"ST_Distance_Sphere_1" 
FROM table 
WHERE ST_Distance_Sphere(table.geometry, ST_GeomFromWKB(:ST_GeomFromWKB_3, :ST_GeomFromWKB_4)) < :ST_Distance_Sphere_2

the returned result is like:

id  parent_id   key            value          geometry                 ST_Distance_Sphere_1
2   3788923     natural        water          POINT (-0.47833 52.25222)  9906.627366
5   3865117     leisure       sports_centre   POINT (-0.50365 52.16449)  0.333864
9   4082699     addr:country    GB            POINT (-0.49252 52.16019)  896.977083
12  4082701     addr:country    GB            POINT (-0.49220 52.16020)  915.175355
16  4082703     addr:country    GB            POINT (-0.49205 52.16015)  927.182032

the query takes long time to execute, so tried to add index with

CREATE INDEX geo ON table USING GIST ( geometry );

but it did not help at all

so I tried to remove this index, having the drop INDEX never finish.

I need the ways to speed-up the query.

Also, as well quering individual locations, I will need query large arrays of locations at a time.

I am entirely new to PostgreSQL.

_____edit_____

EXPLAIN in presence of the index returns the following:

[('Gather  (cost=1000.00..3863720.18 rows=2741158 width=68)',),
 ('  Workers Planned: 2',),
 ('  ->  Parallel Seq Scan on osm  (cost=0.00..3588604.38 rows=1142149 width=68)',),
 ("        Filter: (st_distance_sphere(geometry, 
'0101000020E6100000BEDEFDF15E6D21C0EF74E789E74E4440'::geometry) < 
'2000'::double precision)",)]

'EXPLAIN' having the index dropped:

[('Gather  (cost=1000.00..3863720.18 rows=2741158 width=68)',),
 ('  Workers Planned: 2',),
 ('  ->  Parallel Seq Scan on osm  (cost=0.00..3588604.38 rows=1142149 
width=68)',),
 ("        Filter: (st_distance_sphere(geometry, 
'0101000020E6100000BEDEFDF15E6D21C0EF74E789E74E4440'::geometry) < 
'2000'::double precision)",)]
  • we need to see the output of EXPLAIN on the query both with and without the index – Ian Turton May 23 at 9:29
  • @IanTurton, I am not sure that is what you expect to see, but I havermanaged to get the following. That is in the presence of the index. I can not quickly query the table without index, as it takes forever to remove for some reason: [('Gather (cost=1000.00..3863720.18 rows=2741158 width=68)',), (' Workers Planned: 2',), (' -> Parallel Seq Scan on osm (cost=0.00..3588604.38 rows=1142149 width=68)',), (" Filter: (st_distance_sphere(geometry, '0101000020E6100000BEDEFDF15E6D21C0EF74E789E74E4440'::geometry) < '2000'::double precision)",)] – Dmitriy Grankin May 23 at 9:43
  • I have managed to drop the index and surprisingly the EXPLAIN outputs seem identical to me – Dmitriy Grankin May 23 at 10:08
  • 6
    Nothing suprising about that; your query cannot use any (spatial) index, since there is no invocating filter condition. Right now, you ask PG to traverse the whole table (Seq scan) and calculate distances between all points and the given EWKT geometry. You want to use ST_DWithin(geometry::GEOGRAPHY, <EWKT_Geom>::GEOGRAPHY, <distance>) and have an index USING GIST (CAST(geometry AS GEOGRAPHY)) in place. On a sidenote: ST_Distance_Sphere is a deprecated signature, use ST_DistanceSphere. – geozelot May 23 at 10:28

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.