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?