2

I am attempting a relatively simple task but have been running into trouble. I have two tables, one which has lat and lon points which were geocoded using google (SRID 3857) and another table which contains block boundaries obtained from the Census (SRID 4269). Below are the steps I have taken.

After converting the google map points to geometry. I attempted to make things easier for myself by calculating the centroids of the block boundaries with the following command

CREATE TABLE mytable AS SELECT var1, var2, ST_Centroid(geom) as geom FROM oldtable;

Next I attempted to convert the centroids in the new table to SRID 3857 using the following

CREATE TABLE block3857 AS SELECT var1, var2, ST_Transform(geom, 3857) as geom FROM mytable;

After creating a geospatial index on both the new census table and the table from google maps I attempted to merge the google maps geometry with the census centroids as follows:

          CREATE TABLE merged AS
            SELECT a.var1,b.var2, b.var3
            FROM googlemapstable as a,block3857 as b
            WHERE ST_DWithin(a.geom,b.geom,4828.032)

To my surprise the merge yielded no results. I plotted some of the points on top of each other in R and found that many of them were overlapping and should have merged. My next step to troubleshoot was to look at the distances between the points. I took only a small subset of the points in one city and found that ST_Distance was yielding results of almost 1,000,000 units.

I took a look at the geometry columns' underlying lat and lon points and found that the google map points looked normal for the US, but the census lat and lon points were different than i have ever seen, with the whole number portion of the point being 8 digits long. I went back and found that this change in the lat/lon occured AFTER converting to SRID 3857. Does anyone know if the ST_Distance function makes use of the lat and lon points to do the calculation and if this is what is causing my problem? Does anyone have any solutions to what is going on here?

Improve this question
4
  • You really don't want to be measuring distances in Web Mercator (Greenland is 1/7th the size of South America), even if both shapes were in the same coordinate system.
    – Vince
    Apr 10, 2015 at 19:38
  • Thanks for the response. As a side note, I did attempt to convert both tables to SRID 2163 but was running into the same problem. When I solve my larger problem however, I will definitely consider calculating the distances in another SRID.
    – Justin Klevs
    Apr 10, 2015 at 19:49
  • 3
    (1) Are you sure you understand your two SRSs? You state one table "has lat and lon points which were geocoded using google (SRID 3857)" but EPSG 3857 is a planar coordinate system, using N,E coords, not geographic (lat,lon) coords. (2) You don't say how you go about "converting the google map points to geometry". (3) ST_SRID() will tell you what actual SRID PostGIS thinks an argument is in, even if you think it is something different. Use it to check geometries/geographies in each table. (4) If PostGIS is ignorant of the SRID and has the correct coords, use ST_SetSRID to tell PostGIS.
    – Martin F
    Apr 11, 2015 at 3:27
  • 1
    Oh, and welcome to GIS SE! After considering my points above, please edit any extra info into the body of the question for clarification.
    – Martin F
    Apr 11, 2015 at 3:29

1 Answer 1

Reset to default
3

I suspect that your google SRID may be incorrect.

I believe that google geocode results use SRID 4326 (WGS 84).

I believe the google uses 3857 for the map display, but outputs the data as 4326.

Improve this answer
1
  • Yes! This is what I discovered after reading Martin F's helpful comment. Luckily it was a quick and easy fix. Thanks for responding.
    – Justin Klevs
    Apr 20, 2015 at 21:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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