[postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1 onWindows2003overlap
Mark Cave-Ayland
m.cave-ayland at webbased.co.uk
Tue Jan 25 01:07:39 PST 2005
Hi Randy,
I get exactly the same results as you on Linux and Windows. Here is my
reproducable test script from your email:
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)');
postgis100rc1=# 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);
id | name | astext
----+------+--------
(0 rows)
postgis100rc1=# select astext(transform(GeometryFromText('BOX3D(284519.3
3769952.1,496251.69999999995 3596407.1)'::box3d,26712),4269));
astext
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-----------------------------------
POLYGON((-113.293895748635 32.4858065872829,-113.335183599007
34.0499185167345,-111.041304089383 34.0721027704559,-111.040564853023
32.5067173345671,-113.293895748635 32.4858065872829))
(1 row)
postgis100rc1=# select id, type as name, AsText(the_geom) from ibus where
type='SOR' and the_geom && GeometryFromText('POLYGON((-113.293895748635
32.4858065872829,-113.335183599007 34.0499185167345,-111.041304089383
34.0721027704559,-111.040564853023 32.5067173345671,-113.293895748635
32.4858065872829))', 4269);
id | name | astext
-----+------+------------------------------
105 | SOR | POINT(-111.84864 33.633904)
781 | SOR | POINT(-111.881256 33.580818)
(2 rows)
Very strange - this seems like a bug from here. Strk, I was looking at
whether the SRID was getting lost in the BOX3D conversion, but AsText()
doesn't output the SRID (since it's not part of the OGC spec) it's quite
hard to do. Is there a way to get the full 'SRID=x;POINT(x y)' output for a
geometry?
Kind regards,
Mark.
------------------------
WebBased Ltd
South West Technology Centre
Tamar Science Park
Plymouth
PL6 8BT
T: +44 (0)1752 791021
F: +44 (0)1752 791023
W: http://www.webbased.co.uk <http://www.webbased.co.uk/>
-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Randy
George
Sent: 24 January 2005 18:05
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1
onWindows2003overlap
Hi Jean David,
Thanks for checking. I am still baffled.
As a further check I installed the PG8.0/PostGIS 1.0rc1 on another Windows
XP system and attempted the same query but again the result is empty?
I then went back to Windows 2003 and reinstalled from scratch a new download
of PG8.0 and PostGIS 1.0rc1 again empty results?
I have run the test query from PGAdminIII, DBTool Manager, and
Eclipse DB Browser all with same empty results?
There must be some simple explanation of where I've gone wrong?
Thanks
Randy
_____
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of TECHER
Jean David
Sent: Friday, January 21, 2005 10:16 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1 on
Windows2003overlap
Hi George
Your general query (from linux with your own data from yoru e-mail)
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);
works for me on windows xp with the same expected results
----------------------------------------------------------------
TECHER Jean David
Responsable Informatique 01MAP
e-mail: davidtecher at yahoo.fr
Bureau: 04 67 45 60 27
Portable: 06 85 37 36 75
site perso : http://techer.pascal.free.fr/postgis/
site pro: http://www.01map.com/download/
K-S:"The greatest trick the devil pulled off was convincing people he didn't
exist"
------------------------------------------------------------
----- Original Message -----
From: Randy <mailto:rkgeorge at cadmaps.com> George
To: 'PostGIS Users <mailto:postgis-users at postgis.refractions.net>
Discussion'
Sent: Friday, January 21, 2005 5:58 PM
Subject: RE: [postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1 on Windows
2003overlap
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
_____
_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20050125/fa538ff1/attachment.html>
More information about the postgis-users
mailing list