[postgis-users] Re: PG 8.0 and PostGIS 1.0 RC1 onWindows2003overlap
strk at refractions.net
strk at refractions.net
Tue Jan 25 01:24:51 PST 2005
Mmm.. I wouldn't use GeometryFromText with a BOX3D argument.
I think it should only accept 'text' as it has been done with GeomFromWKB
only accepting bytea.
Beside this, you can use asEWKT(geometry) to see Extended WKT.
.. And .. I've found the bug.
The transform() functoin is failing to transform/recompute an already
cached bbox.
I'm working on it.
--strk;
On Tue, Jan 25, 2005 at 09:07:39AM -0000, Mark Cave-Ayland wrote:
> 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
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list