2

I am mostly relying on 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.

What I did is based on the post I referenced saying that google geocoding already comes in 4326 format.

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.

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.

closed as too broad by PolyGeo May 30 at 10:06

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

It looks fine and the distance is always measured in the units the SRID used defines. For 4326 that would be in angular units. Unless you use geographies like you found out, that is. Read more in the official docs here.

Why 100 km is needed for Chicago is up to you and your data. It doesn't sound that much for a huge city and in fact if I check Wikipedia, the area of the metropolitan part is just a bit below that of a circle with a 100 km radius.

  • thanks so much for your response! It's a relief not to have to push further in the project without knowing whether my code makes sense. I will look into changing to a different SRID in that case to get a mile or meter based unit. – Sunnyside Jan 16 '15 at 20:56
  • If you want to get to get the distance in meters, use ST_Distance_Sphere or ST_Distance_Spheroid. You can still keep the geometries in 4326. – John Powell Jan 16 '15 at 22:04
  • @JohnBarça thanks! I was about to start looking into that. – Sunnyside Jan 16 '15 at 23:53

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