1

I am doing a tracking query. The device is sending its location data, timestamp to store in MySQL Database. I want to show the query result of every point with its nearest POI. The POI database is stored in PostgreSQL database as PostGIS format. Its taking 8-9 seconds to return the query result. At first I am calling the longitude latitude values from MySQL Database then calling the nearest POI info for every point from PostgreSQL database using while loop. I want to know is there any way to reduce the query time? I am using the following query

SELECT  name, area, district, ST_Distance(geom::geography,ST_GeographyFromText('POINT(
$longitude $latitude
)')) as distance 
FROM poi 
ORDER BY distance
Limit 1;

I need to get a JSON result to visualize on map.

  • Yes, you can use the Order by <-> approach for knn. – John Powell Feb 17 '16 at 13:42
  • You should also have an index on the poi table; Make sure that it is using the geography since that is what you are using in your query – Devdatta Tengshe Feb 18 '16 at 6:54
2

The following query solve my issue

SELECT name, area, district, ST_Distance_Sphere(geom,'POINT($longitude $latitude)'::geometry) as distance 
FROM poi
ORDER BY
poi.geom <->'POINT($longitude $latitude)'::geometry
LIMIT 1;
  • 1
    This seems to work in a similar way. The <-> operator makes use of the spatial index during sorting and will speed up the query accordingly – Thomas Feb 19 '16 at 4:13
1

I assume you can't change the setup with the two databases. So let's take this as a given.

Your postgis query is very inefficient. Although you only display the first row it still computes distances to all points in the table.

Is there a max distance you want to search in? You could add a where clause to limit the calculation to points which intersect with a certain buffer around your POI. also make sure you have a spatial index on your point layer.

  • Can you give any example of use where clause or elaborate how to have a spatial index on the point layer? – Devils Dream Feb 18 '16 at 5:16

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.