<div dir="ltr">Lee, Paul,<div>Thanks so much for your time.<br>Lee, </div><div>My understanding is that there are about 42,000 zips in the US, So a cross join should result in about 1.7 Billion entries into the <span style="font-size:12.8px">postal_zone_distance table</span>. Maybe that's not so bad, I'll have to run the numbers. Where as this might be the fastest possible solution, it's probably over-kill for my meager needs. I understand what you mean by needing to know first the SRID of the Latitude and Longitude from my source table. It means very little without knowing the Datum from which they were derived. Thanks to some youtube videos I think I grasp this. </div><div><br></div><div>You said:</div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span style="font-size:12.8px"><i> ...while lat-long SRIDs (like 4326) say the units are "meters", ST_Distance and other distance and area functions always just work in Cartesian coordinates, meaning they treat long and lat as X and Y in a Cartesian plan and they take parameters and return answers in degrees, which isn't really meaningful.</i></span></blockquote><div> </div><div> I believe you and my experience seems to confirm it, however I can't seem to reconcile this with the documentation for ST_Distance which says ... </div><div><br></div><div> <a href="http://postgis.net/docs/ST_DWithin.html">http://postgis.net/docs/ST_DWithin.html</a> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px"><i>For Geometries: The distance is specified in units defined by the spatial reference system of the geometries. For this function to make sense, the source geometries must both be of the same coordinate projection, having the same SRID.</i></span></blockquote><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px"><br></span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px">Is the documentation wrong??? or am I just not interpreting it correctly. </span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px"><br></span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px">If it IS wrong, for god sake I hope they fix it as I have now just wasted too much of everybody's time on and issue that could have been cleared up by better documentation.<br><br>Again, thanks to everybody who replied, you are most generous. </span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px">Regards,</span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px">Mike</span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px"><br></span></div><div><span style="color:rgb(46,46,46);font-family:'Lucida Grande',Verdana,Geneva,Arial,Helvetica,sans-serif;font-size:13.3333px"><br></span></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Tue, Apr 12, 2016 at 9:26 AM, Lee Hachadoorian <span dir="ltr"><<a href="mailto:Lee.Hachadoorian+L@gmail.com" target="_blank">Lee.Hachadoorian+L@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr">Michael,<div><br></div><div>There are many ways to accomplish this, and the most efficient approach is going to depend on what else you are using this data for. If literally the *only* thing you are using the geometries for is to calculate distances between postal zones, I would consider creating a table like:</div><div><br></div><div>CREATE TABLE postal_zone_distance (</div><div> source_zone text,</div><div> dest_zone text,</div><div> distance_m double precision,</div><div> PRIMARY KEY (source_zone, dest_zone)</div><div>);</div><div><br></div><div>and populating it *once* with distances between all postal codes in the US, so that you can run future queries using an attribute select (WHERE distance_m < 50000) instead of a spatial select. Rerun whenever postal zones change, which shouldn't be all that often. You can populate it with something like:</div><div><br></div><div>INSERT INTO postal_zone_distance</div><div>SELECT a.postalcode, b.postalcode, ST_Distance(a.geo_position::geography, b.geo_position::geography)</div><div>FROM tpostalcoordinate a CROSS JOIN tpostalcoordinate b;</div><div><br></div><div>Will take a while to run, but future queries will be faster.</div><div><br></div><div>If you intend to use this for more than just these distances, there are several options, all of which are valid, but again it depends what you want to use it for. The main issue between using Paul's suggestion to just define columns as geography vs keeping in geometry is whether you might visualize the postal zones using a GIS. If you will be using QGIS or ArcGIS to connect to PostGIS to map the zones, you will need to keep in geometry. In this case you can, as Lucas suggest, define a functional index on the geography cast, while keeping the underlying data in geometry.</div><div><br></div><div>Some other notes:</div><div><br></div><div>* To get to your original question about units of measurement, while lat-long SRIDs (like 4326) say the units are "meters", ST_Distance and other distance and area functions always just work in Cartesian coordinates, meaning they treat long and lat as X and Y in a Cartesian plan and they take parameters and return answers in degrees, which isn't really meaningful. There are special functions like ST_Distance_Spheroid which take lat-long geometry and return distance in meters on the spheroid. And ST_Distance on geography type also returns meters.</div><div><br></div><div>* Be careful about your choice of SRID. The fact that you tried to transform (or assign?) this to EPSG 4896 is a red flag because because one shouldn't just "try" SRIDs. Your data come to you in an SRID, and you should only transform it for a particular purpose. In fact you should make sure that you know the datum of your original lat-long geometries. Considering that the data are for the US, it is quite possible that your data are in EPSG 4269 (lat-long North American Datum 1983, used for much US government data including by the Census Bureau), rather than being EPSG 4326 (World Geodetic System 1984, the datum used by GPS satellites).</div><div><br></div><div>I highly recommend Obe & Hsu's PostGIS in Action (<a href="https://www.manning.com/books/postgis-in-action-second-edition" target="_blank">https://www.manning.com/books/postgis-in-action-second-edition</a>), esp. Ch 3 on Spatial Reference System Considerations.</div><div><br></div><div>Best,</div><div>--Lee</div><span class="HOEnZb"><font color="#888888"><div><br></div><div>-- <br><div>Lee Hachadoorian<br>Assistant Professor of Instruction, Geography & Urban Studies<br>Assistant Director, Professional Science Master's in GIS<br>Temple University</div></div></font></span><div><div class="h5"><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Apr 11, 2016 at 11:00 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">Go back to basics, use a geography column from the start:<br>
<br>
ALTER TABLE tpostalcoordinate ADD COLUMN geog geography(point, 4326);<br>
UPDATE tpostalcoordinate SET geog =<br>
geography(ST_SetSRID(ST_MakePoint(longitude, latitude), 4326));<br>
CREATE INDEX tpostalcoordinate_gix ON tpostalcoordinate USING GIST (geog);<br>
SELECT a.*<br>
FROM tpostalcoordinate a<br>
JOIN tpostalcoordinate b<br>
ON ST_DWithin(a.geog, b.geog, 10000)<br>
WHERE<br>
a.countrycode2tcountry = 'US' AND<br>
b.countrycode2tcountry = 'US' AND<br>
b.postalcode = '94404';<br>
<br>
A geocentric system is one where every point is referenced relative to<br>
the center of the earth, so they all have an x,y,z. It's not something<br>
you want to use.<br>
<span><font color="#888888"><br>
P<br>
<br>
</font></span><span><br>
On Mon, Apr 11, 2016 at 12:31 PM, Michael Moore <<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.com</a>> wrote:<br>
><br>
><br>
</span><div><div>> On Mon, Apr 11, 2016 at 11:50 AM, Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>><br>
> wrote:<br>
>><br>
>> Before I can even begin to address what's going on with the<br>
>> index/query side, I have to ask about the coordinate system:<br>
>><br>
>> You are using a geocentric system for postal code points?<br>
>><br>
>> <a href="https://epsg.io/4896" rel="noreferrer" target="_blank">https://epsg.io/4896</a><br>
>><br>
>> This seems very odd indeed. If you were flying a satellite or drilling<br>
>> a deep well, maybe you'd use geocentric coordinates, but simple postal<br>
>> code queries? So, perhaps there's a core error to deal with first: why<br>
>> are you in EPSG:4896?<br>
>><br>
>> P<br>
>><br>
>><br>
>> On Mon, Apr 11, 2016 at 11:21 AM, Michael Moore <<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.com</a>><br>
>> wrote:<br>
>> > I am trying to find all zip codes withing a given range of current zip<br>
>> > code.<br>
>> > For example, User is at zip code 95076 and want to know all zip codes<br>
>> > within<br>
>> > a 30 mile range. This will always be a short distance, nothing over 50<br>
>> > miles. The source zip code is any place in the USA. My input table has<br>
>> > a<br>
>> > latitude field, a longitude field and a POINT field for each zip code.<br>
>> > I want to supply in input in meters, not degrees. I do NOT want to cast<br>
>> > to<br>
>> > geography because this prevents the index from being used.<br>
>> > Here is a working query that uses the index, but ST_DWithin is not<br>
>> > understanding ".05" as meters:<br>
>> ><br>
>> ><br>
>> > lcd1_dev=> select t2.city, t2.postalcode,<br>
>> > ST_Distance(t1.geo_position,t2.geo_position)<br>
>> > lcd1_dev-> from tpostalcoordinate t1<br>
>> > lcd1_dev-> left join tpostalcoordinate t2 on<br>
>> > ST_DWithin(t1.geo_position,t2.geo_position , .05)<br>
>> > lcd1_dev-> where t1.postalcode = '94404'and t1.countrycode2tcountry =<br>
>> > 'US'<br>
>> > and t2.countrycode2tcountry= 'US';<br>
>> > city | postalcode | st_distance<br>
>> > --------------+------------+---------------------<br>
>> > Redwood City | 94065 | 0.0273766323714193<br>
>> > San Mateo | 94408 | 0.00504738546179631<br>
>> > Belmont | 94002 | 0.0440065904155286<br>
>> > San Mateo | 94404 | 0<br>
>> > San Mateo | 94403 | 0.0370314731005901<br>
>> > San Mateo | 94407 | 0.0416118372581607<br>
>> ><br>
>> > This shows that I am using SRID 4896<br>
>> > lcd1_dev=> select * from geometry_columns where f_table_name =<br>
>> > 'tpostalcoordinate';<br>
>> > f_table_catalog | f_table_schema | f_table_name |<br>
>> > f_geometry_column |<br>
>> > coord_dimension | srid | type<br>
>> ><br>
>> > -----------------+----------------+-------------------+-------------------+-----------------+------+-------<br>
>> > lcd1_dev | qsn_app | tpostalcoordinate | geo_position<br>
>> > |<br>
>> > 2 | 4896 | POINT<br>
>> ><br>
>> > 4896 is UNIT "metre" as show here:<br>
>> > 4896 | EPSG | 4896 |<br>
>> ><br>
>> > GEOCCS["ITRF2005",DATUM["International_Terrestrial_Reference_Frame_2005",SPHEROID["GRS<br>
>> > 1980",6378137,298.257222101,AUTHORIT<br>
>> ><br>
>> > Y["EPSG","7019"]],AUTHORITY["EPSG","6896"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["metre",1,AUTHORITY["EPSG","9001"]],AXIS["Geocentric<br>
>> > X",OTH<br>
>> > ER],AXIS["Geocentric Y",OTHER],AXIS["Geocentric<br>
>> > Z",NORTH],AUTHORITY["EPSG","4896"]] | +proj=geocent +ellps=GRS80<br>
>> > +units=m<br>
>> > +no_de<br>
>> ><br>
>> > The following (from EXPLAIN) proves that the index is being used in the<br>
>> > working query shown above:<br>
>> > -> Index Scan using tpostalcoordinate_pk on tpostalcoordinate t1<br>
>> > (cost=0.42..8.45 rows=1 width=32)<br>
>> ><br>
>> > What do I need to do to get this in meters without losing my index<br>
>> > access?<br>
>> ><br>
>> > TIA,<br>
>> > Mike<br>
>> ><br>
>> > _______________________________________________<br>
>> > postgis-users mailing list<br>
>> > <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> > <a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
><br>
><br>
> Here is what we do to create the column on the table:<br>
><br>
><br>
><br>
> 1. SELECT AddGeometryColumn('qsn_app','tpostalcoordinate',<br>
> 'geo_position',4326,'POINT',2);<br>
><br>
><br>
><br>
> 2. UPDATE tpostalcoordinate pc set pc.geo_position =<br>
> ST_SetSRID(ST_Point(pc.longitude, pc.latitude), 4326);<br>
><br>
><br>
><br>
> 3. CREATE INDEX tpostal_geo_position_idx ON tpostalcoordinate USING<br>
> gist(geo_position);<br>
><br>
><br>
> I am on EPSG:4896 because I tried about 10 others that were also meters and<br>
> that's just the one I ended up on . There is one thing you said that leads<br>
> me to believe there is something I don't understand: "...geocentric<br>
> system...". My understanding is that there are two datatypes to choose from<br>
> ; geometry and geography. I have chosen geometry because 1. it should be<br>
> faster due to simpler calculations and 2. It should be faster because it can<br>
> use the index. Beyond that I thought that I would then need to choose a<br>
> proper SRID. The two criteria for choosing an SRID for me are 1. the units<br>
> are meters and 2. it is based on a datum that is appropriate for the USA.<br>
> Now the fact that you said: "geocentric system" makes me think that maybe<br>
> there is something else I need to be looking at when choosing a proper SRID.<br>
> A little more on the application: The user wants to know all of the schools<br>
> that are within a specific distance of his current location, his zip code.<br>
> Precision is not important. We get the user's zip code from the screen and<br>
> we have a list of schools and their zip codes.<br>
><br>
><br>
> Thanks,<br>
><br>
> Mike<br>
><br>
><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></div></div></blockquote></div><br>
<br></div></div></div></div>
</blockquote></div><br></div>