Sorry to ask a basic question, but I have not been able to find this laid out entirely anywhere. I am mostly relying on this SO post: What Spatial Reference System do I store Google Map's Lat/Lng in
My web application takes user addresses, queries the Google geocoding API for coordinates, and then stores those coordinates in a postgresql postgis-enabled database. To do this I run the following insert command:
$query = "INSERT INTO userloc (email, latitude, longitude) VALUES($1, $2, $3)"; $rs = pg_query_params($con, $query, array($emailN, $latitude, $longitude));
followed by a command within postgresql:
UPDATE userloc SET the_geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
I am only storing data for a few hours (far less than 30 days), so my understanding is I am not violating google TOS.
Have I done things right so far? What I did is based on the SO post I referenced saying that google geocoding already comes in 4326 format.
My question is, once I have a collection of these points and I want to find all points within a certain range of a particular longitude, latitude pair, what query do I run? I have tried the following:
$query = "SELECT * FROM userloc WHERE ST_DWithin(the_geom, 'POINT(".$longitude." ".$latitude.")', 100.0)"; $rs = pg_query($con, $query);
When I run this, I get the following error:
pg_query(): Query failed: ERROR: Operation on two GEOMETRIES with different SRIDs in /var/www/html/signup.php on line
If I try running this instead, using ST_SetSRID:
$query = "SELECT id FROM userloc WHERE ST_DWithin(the_geom, ST_SetSRID(ST_MakePoint(".$longitude.", ".$latitude."),4326), 100.0)"; $rs = pg_query($con, $query);
I don't get an error, but the search doesn't make sense. Or it might make sense if it I knew more about geometric units because the distance parameter (100 above) clearly doesn't correspond to normal distance. If I make it very small it does cut to the nearest (a cluster in NY), but if I make it just slightly larger it starts including points in Chicago.
So I thought this probably had something to do with geography vs. geometry. I am interested in geographic searches, so then I thought I should convert to geography like so:
$query = "SELECT id FROM userloc WHERE ST_DWithin(the_geom::geography, ST_SetSRID(ST_MakePoint(".$longitude.", ".$latitude."),4326)::geography, 100000.0)"; $rs = pg_query($con, $query);
This really seems to do the trick (can distinguish nearness within cities), but the units are still baffling. I thought the default units were meters, but I had to enter the # above, 1 with 5 0's to get all the points in Chicago to be included in a Chicago-oriented search, so I don't think it is meters.
So two questions. Can someone confirm that what I have done makes sense? I am sorry I am so new to gis in general, so I am struggling to get a hold of all the different systems of measurements, data storage.
Second, can someone tell me what the distance is in my last command or how I can find out? I need to be able to set a specific distance.
Finally, I apologize if this has duplicated a post. I thought it would be easy to find step-by-step instructions, but I suppose concerns about the google TOS means that most people don't go this route.
Thanks for any advice/feedback.