[postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1 on Windows 2003 overlap

[Randy George]

Hi,

 

            I successfully installed a version of PostGIS on a test windows
system thanks to Mark's installer. However I am running into a problem with
a simple spatial query. The original query works fine on an earlier Linux
setup. For some reason the identical spatial query using a box3d transform
from srid=26712 ( utm83-12m ) to srid=4269 (LL)  works on the previous setup
but not the new windows setup?

Perhaps someone could help point out my error below:

 

Previous query using Linux PG 7.4 PostGIS 0.8.0 (I've included a test table
at the end.)

select id, type as name, AsText(the_geom) from ibus where type='SOR' and
the_geom && transform(GeometryFromText('BOX3D(284519.3
3769952.1,496251.69999999995 3596407.1)'::box3d,26712),4269);

 

results OK:

105;"SOR";"POINT(-111.84864 33.633904)"

781;"SOR";"POINT(-111.881256 33.580818)"

 

New test using PG 8.0 and PostGIS 1.0 RC1 on Windows 2003

select id, type as name, AsText(the_geom) from ibus where type='SOR' and
the_geom && transform(GeometryFromText('BOX3D(284519.3
3769952.1,496251.69999999995 3596407.1)'::box3d,26712),4269);

results empty?

 

Test1: PG 8.0 and PostGIS 1.0 RC1 on Windows 2003

This verifies correct transform?

select AsText(transform(GeometryFromText('BOX3D(284519.3
3769952.1,496251.69999999995 3596407.1)'::box3d,26712),4269));

"POLYGON((-113.293160109785 32.4857520041516,-113.334429907594
34.0498920281307,-111.040622350418 34.0720603897317,-111.039903423973
32.5066459734368,-113.293160109785 32.4857520041516))"

 

Test2: PG 8.0 and PostGIS 1.0 RC1 on Windows 2003

This verifies overlap with SRID=4269

select id, type as name, AsText(the_geom) from ibus where type='SOR' and
the_geom && GeometryFromText('BOX3D(-113.3344299323
34.0498929284,-111.0399034240 32.5066459726)'::box3d,4269)

 

results OK:

105;"SOR";"POINT(-111.84864 33.633904)"

781;"SOR";"POINT(-111.881256 33.580818)"

 

However combining overlap with the box3d transform gives an empty result
set?

 

Test table:

CREATE TABLE "public"."ibus" 

(

            "id" integer NOT NULL DEFAULT
nextval('public.ibus_id_seq'::text),

            "type" varchar(3),

            "unitid" varchar(40),

            "groupcode" varchar(40),

            "personid" varchar(40),

            "triptype" varchar(10),

            "latitude" float8,

            "longitude" float8,

            "time" timestamp,

            "gpsdatavalid" varchar(2),

            "the_geom" geometry,

            CONSTRAINT "ibus_pkey" PRIMARY KEY ("id"),

            CONSTRAINT "$1" CHECK ((srid(the_geom) = 4269)),

            CONSTRAINT "$2" CHECK (((geometrytype(the_geom) = 'POINT'::text)
OR (the_geom IS NULL)))

);

CREATE INDEX ibus_idx ON ibus USING gist (the_geom);

INSERT INTO "public"."ibus" ("id", "type", "unitid", "groupcode",
"personid", "triptype", "latitude", "longitude", "time", "gpsdatavalid",
"the_geom") VALUES(105, 'SOR', 'SCO6aa29-f25f-4744-83dc-4a5ecc3ea192',
'2004-12-15T15:37:55.506Z', '00000000-0000-0000-0000-000000000000', 'R',
33.633904, -111.84864, '2004-12-15 15:37:55.506', '1',
'SRID=4269;POINT(-111.84864 33.633904)');

INSERT INTO "public"."ibus" ("id", "type", "unitid", "groupcode",
"personid", "triptype", "latitude", "longitude", "time", "gpsdatavalid",
"the_geom") VALUES(781, 'SOR', 'SCO6aa29-f25f-4744-83dc-4a5ecc3ea192',
'2004-12-18T04:38:38.541Z', '00000000-0000-0000-0000-000000000000', 'R',
33.580818, -111.881256, '2004-12-18 04:38:38.541', '1',
'SRID=4269;POINT(-111.881256 33.580818)');

 

Thanks

Randy

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050121/25fd0ad4/attachment.html>