st_distance with an added Integer value is not working in 2.0

[vbalent]

I have two PostGIS environments (Windows Server and Linux) My Linux Version does not retrun records and throws no errors;

I have built a simple SQL function in that 1) transforms my Lat Longs into State Plane US Feet WORKS 2) measure the distance between the two points and buffer it by 1 mile (5280) US Feet DOES NOT WORK

However No Errors are thrown, just No Records are returned.

SQL Statement

SELECT P1.* FROM PART_1_CRIMES P1 WHERE P1.PART_I_CRIME IN ('MURDER','RAPE','ROBBERY','AGGRAVATED_ASSAULT','BURGLARY','LARCENY','MV_THEFT','MV_LARCENY')

AND ST_DISTANCE(ST_TRANSFORM(ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260),P1.GEOM)⇐5280 ORDER BY P1.BEGINNING_DATE DESC LIMIT 1000;

This exact same SQL statement works 100% as expected in this Version:

PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 32-bit POSTGIS="1.5.5" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.6.1, 21 August 2008" LIBXML="2.7.8" USE_STATS

However not in this Version:

PostgreSQL 9.2.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit POSTGIS="2.0.3 r11128" GEOS="3.3.

8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.6" LIBJSON="UNKNOWN" TOPOLOGY

Please advise?

Regards Val 518-457-5905

[robe]

Val,

Minor suggestion, you should be using ST_DWithin(geom1,geom2,5280) instead of sT_Distance because ST_Distance will never use an index.

That aside these issues are often caused by missing nadgrid files. An easy verification:

SELECT ST_AsText(ST_Transform(ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260));

on both systems and verify they return the same answer.

[vbalent]

Hi,

I did check the nadgrid files and they are both reporting the exact same return values in Windows Server and Linux.

I am trying to keep this simple for now the SQL statement below, however The st_distance worked perfect from above, I know it does not utilize the Gist. But it works on Windows Server and not on Linux.

This SQL statemant errors but I can not find a Sample code where I can say =5280 feet for a Mile Radius.

SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(geom, 'POINT(687884 1355669)', 2260) =5280.0;

[robe]

You should right it like this below. It's example like you Distance ⇐ 5280 except the distance value is part of the function. ​http://postgis.net/docs/manual-2.0/ST_DWithin.html

SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(geom, ST_TRANSFORM(ST_GeomFromText('POINT(-73.76545859999999 42.658618)', 4326), 2260),  5280.0) ; 

You can use ST_PointFromText as you have but ST_GeomFromText works for all geometries and is also faster speed wise. ST_PointFromText does an extra check to verify you gave it a point which is unnecessary for your use case.

BTW: this is all covered in first chapter of my upcoming 2nd edition book: ​http://www.postgis.us/chapter_01_edition_2 and first chapter is a free download

[robe]

I forgot to ask — did you verify you get the same answer on both servers for this query — I just tried my 1.5 and 2.0 installs on separate servers and got the same answer.

SELECT ST_AsText(
   ST_Transform(
     ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260)
);

-- answer  POINT(689696.298242221 1394398.10155405)

Its possible that even though you have nadfiles your proj environment variable is not set right to read it. For windows it uses the contrib/postgis.. location if no proj environment variable is set. I don't think this is the case for Linux and I can't remember off hand what the variable is called since I haven't had to set it in a while.

[vbalent]

Hi, I forgot to ask — did you verify you get the same answer on both servers for this query — I just tried my 1.5 and 2.0 installs on separate servers and got the same answer.

Yes I did check and received the exact same numbers in both environments.

I wish you could help find this proj environment variable in Linux.

On a side note when I do bump my radius from 1 mile (5280 Feet), No records, However when I bump the Radius search to like 300 miles out I DO get records returned.

Any thuoghts?

Val

[vbalent]

Ok i ran a command call set in Linux which gives me back all my System Setting and Variables. I did a find and did not see anything related to PostGIS

I have attached it any thoughts?

[vbalent]

Linux Path, Varaiables

[robe]

Hmm don't see a PROJ_LIB set, and I think if not set it uses the default location which might vary depending on OS. Details described here: ​http://home.gdal.org/projects/wcts/install.html

BUT: YOU STILL DID NOT ANSWER MY QUESTION: What does this query return?

SELECT ST_AsText(
   ST_Transform(
     ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260)
);

Mine returns: — answer POINT(689696.298242221 1394398.10155405)

If you get the same or similar answer then proj is not your issue and you probably have a more obvious problem of your datasets are simply different on the two systems.

If you get a different answer, then proj is your issue.

[vbalent]

Thanks,

For all your help.

I finally got the Query to work.

SELECT * FROM PART_1_CRIMES where ST_DISTANCE(ST_TRANSFORM(ST_POINTFROMTEXT('POINT(-73.76545859999999 42.658618)', 4326), 2260),

ST_Transform(ST_POINTFROMTEXT(ST_AsText(GEOM),4326),2260))⇐5280;

Was not a proj issue, just getting the syntax correct.

[robe]

That's a little convoluted. The ST_AsText is just for displaying geometry in user friendly way not to actually use. That would be super slow with large geometries.

Now that said I see you are doing this:

ST_Transform(ST_POINTFROMTEXT(ST_AsText(GEOM),4326),2260)

Which suggests your table data is NOT in 2260 projection or if it is it is misstated but really in wgs_84 long lat (assuming your above query works as you are expecting it to).

What does:

SELECT ST_SRID(geom) from part_1_crimes limit 1;

return?

To save yourself having to write convoluted (and super slow code that doesn't use an index) as the above, convert your table to 2260 with a command something like:

ALTER TABLE part_1_crimes ALTER TYPE geom geometry(POINT,2260) USING ST_Transform(ST_SetSRID(geom, 4326),5260);

Then you should be able to rewrite your query as follows:

 SELECT * FROM PART_1_CRIMES WHERE ST_DWithin(ST_TRANSFORM(ST_GeomFromText('POINT(-73.76545859999999 42.658618)', 4326), 2260),

    GEOM, 5280);

and if you have a gist index on your geom column, the above query should run MUCH faster and yield the same results.