[postgis-users] Help loading box (polygon) data, value of GiST on squares and 180 meridian question

[Mark Cave-Ayland]

Hi Brent,

> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net 
> [mailto:postgis-users-bounces at postgis.refractions.net] On 
> Behalf Of Wood Brent
> Sent: 15 April 2004 06:38
> To: PostGIS Users Discussion
> Subject: [postgis-users] Help loading box (polygon) 
> data,value of GiST on squares and 180 meridian question

(bits cut)

> This seems fine. I then write the file with the command to 
> insert a record, the line as written is:
> 
> insert into chat_cell (cell_id,depth,substrate,the_geom) 
> values (1,0,0,GeometryFromText('POLYGON(173.0000 -45.4834, 
> 173.0000 -45.4668, 173.0166 -45.4668, 173.0166 -45.4834)',4326));
> 
> 
> This returns the error message when run:
> psql -d env2003 -f cells.sql
> psql:cells.sql:1: ERROR:  couldnt parse object in GEOMETRY
> 
> I assume my insert command has an error in the geometry formatting???
>
> The object should be a small box, with 4 corners. I'm 
> assuming I don't need to finish with the first point to close 
> the polygon, and I get the same error when I add the first 
> point at the end.

Within the POLYGON(....) definition you need to wrap an extra set of
brackets around each ring of you polygon, plus as you noted you need to
make the last point equal to the first point. So the following should
work for you:

insert into chat_cell (cell_id,depth,substrate,the_geom) values
(1,0,0,GeometryFromText('POLYGON((173.0000 -45.4834, 173.0000 -45.4668,
173.0166 -45.4668, 173.0166 -45.4834, 173.0000 -45.4834))',4326));

> Followup questions, for when (not if :-) I get the data loaded.
> 
> As the table only contains square boxes (about 50,000 when 
> done), is there any benefit to creating a GiST index on it? 
> The index will be the bounding box for each cell, which is 
> the same as the cell, but it may have other advantages I'm 
> not aware of to improve performance, like the tree itself 
> being faster than a table scan.

This really depends on your usage patterns; if this is the only table
being used in your database cluster then with enough RAM and shared
buffers PostgreSQL should keep them in memory. The best way to check
this would be to design a test application to mimic your usage pattern,
and see which settings give the best performance since there are so many
different installations and usage patterns.

> Also, I'm using SRID 4326 as a generic WGS84 lat/long 
> projection. Is this going to be happy with longitudes in the 
> range of 0-360 instead of -180 to +180? (or should I ask this 
> in a proj area instead?) Will -170 equate to 190?

No, I don't think this is the case. I'm guessing that while the
distance/area functions will still work correctly, it would still be
better to use -180 to 180 as I think this is the standard used by the
reprojection libraries and all the maps I've seen (anyone feel free to
correct me on this). Note that PostGIS also has a translate(x,y,z)
function which can probably do this for you, so if you can't change your
data at source you could do something like:

insert into chat_cell (cell_id,depth,substrate,the_geom) values
(1,0,0,translate(GeometryFromText('POLYGON((173.0000 -45.4834, 173.0000
-45.4668, 173.0166 -45.4668, 173.0166 -45.4834, 173.0000
-45.4834))',4326), -180.0, 0.0, 0.0));


Hope this helps,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.