I'm using PostGIS/Tiger for a mapping solution.
For a given longitude, latitude (derived when someone clicks on a point in my Google Map), I want to retrieve the closest point from my table. My table has 5 columns on it:
id, customvalue, the_geom, longitude, latitude
(so I already have a longitude/latitude and geometry for each row)
For a given long/lat, I would like to find the closest row in the database. I achieve it in this way:
SELECT * FROM mytable WHERE ST_Distance_Sphere( ST_Point(ST_X(ST_Centroid(the_geom)), ST_Y(ST_Centroid(the_geom))), (ST_MakePoint(-73.94039154052734, 40.68779755192939))) <= 300
This works and gives me my results! The 300 I just made up as it seems to give me 2-3 (but not thousands). I then just take the first record and i'm done.
The problem is this query takes about 10-13 seconds to run each time. I would love to speed this up.
I have two possible solutions I would like help on:
This query is overkill and not constructed right because I'm not taking advantage of the fact I already have a geometry, long, and lat on each row in my table, and I should construct the query differently.
The query is actually correct, but I am missing some indexes that could help me out and make things much faster.