SQL Statemant Works in Windows version 1.5x Not in Liunx version 2.0x

[vbalent]

This is the second time this week that my ST Functions that worked fine in Windows Server are now not working in Linux.

I truly doubt PostGIS is trying to break a lot of peoples codes?

SQL Statement

SELECT P1.* FROM PART_1_CRIMES P1, "PatrolZones" PZ WHERE P1.PART_I_CRIME IN ('MURDER','RAPE','ROBBERY','AGGRAVATE D_ASSAULT','BURGLARY','LARCENY','MV_THEFT','MV_LARCENY') AND PZ.ID_AS_STRI IN('101','102','103','104','105','106', '107','108','109','110','201','202','203','204','205','206','207','208','209') AND ST_WITHIN(P1.GEOM,PZ.the_geom) ORDER BY P1.BEGINNING_DATE;

This exact same SQL statement works 100% as expected in this Windows 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 Linux 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?

Thanks, Val

[Bborie Park]

What's the projection of P1.GEOM and PZ.the_geom? And are you sure that the geometries are in the projection indicated by the SRID? Your last ticket showed that the geometries were not in the projection of the recorded SRID.

[vbalent]

Hi yes.

Thanks you for helping the last time on my previous ticket and I modified the changes to utilize the Gist Index on the Geom.

Currently my points that I am looking to find in polygons are both in the Same SRID

Ran the following select UpdateGeometrySRID('public', 'PatrolZones', 'the_geom', 2260) ;

Part_1_Crimes are already in 2260

psm_gee=# SELECT ST_SRID(the_geom) from "PatrolZones" limit 1;

st_srid

---

2260

(1 row)

psm_gee=# SELECT ST_SRID(geom) from part_1_crimes limit 1;

st_srid

---

2260

(1 row)

[vbalent]

I have attached 2 screenshots, one from Windows and one form Linux.

Windows Works, Linux does not. I actually would have expected the opposite in my honest opinion.

Same Function call, same SQL Syntax.

Very Baffling.

[Bborie Park]

Have you checked the output of ST_AsEWKT(geom)? Just because the SRID is 2260, doesn't mean the coordinates are.

According to spatialreference.org, coordinates for 2260 should be bounded between 116071.8523, 747947.1638, 837761.3518, 2260847.6417. If your coordinates aren't in those bounds, you have a mismatch between what the coordinates truly are and what projection someone says they are.

[robe]

vbalent based on the last ticket you posted, I suggested you do the below query because given the convoluted query you gave I had already concluded your data is stamped as 2260 but is really in wgs 84 long lat (4326) as Bborie also mentioned above.

Sorry I had a typo in the last ticket could I gave. I apologize for the confusion there. You should run this

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

I'm marking this as invalid again because your issue seems to not be a bug but just a newbie misunderstanding about using PostGIS. Please subscribe to our user's list and post your questions there before assuming they are bugs in the software.

​http://lists.osgeo.org/mailman/listinfo/postgis-users

[robe]

hmm I should probably try running code before I post it. here is yet another typo correction.

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

[vbalent]

Hi,

Yes I checked the 2 different tables I am using.

I used the Function ST_AsEWKT(geom)

My polygon Table is SRID 2260 with True Us East State Plane Coordinates

My points table is SRID 2260 with Lat Long Values.

I will run the command you sent so that they are both in the Same Geometry not just said SRID.

I hope this command works and not break anything on my other apps. I understand they should all be in the same geometry everything.

My point and why I said it might be a defect is that It worked in Windows 64 bit, but now it did not work in Linux 64 bit.

Thanks, Val