[postgis-users] Geometry constructors/casts with WKT, BOX and ST_GeomFromText/ST_GeogFromText
Stefan Keller
sfkeller at gmail.com
Fri Jan 7 17:37:42 PST 2011
These are maybe stupid questions, but I'd like primo to understand the
design principles of PostGIS and know secondo the most efficient
solution.
Let's look at this simple frame / bbox query (which works):
SELECT ST_AsText(geom)
FROM planet_osm_point
WHERE CONTAINS
(
ST_SetSRID('BOX3D(572126 5671279,1258837 6154972)'::box3d,900913),
geom
)
My question is about geometry construction and casting. These all
return the same result (and work fine):
SELECT ST_AsText(ST_SetSRID('BOX(572126 5671279,1258837
6154972)'::box2d, 900913))
UNION
SELECT ST_AsText(ST_SetSRID('BOX3D(572126 5671279,1258837
6154972)'::box3d, 900913))
UNION
SELECT ST_AsText(ST_SetSRID(ST_MakeBox2D(ST_Point(572126, 5671279),
ST_Point(1258837, 6154972)), 900913))
UNION
SELECT ST_AsText(ST_SetSRID(ST_MakeBox3D(ST_Point(572126, 5671279),
ST_Point(1258837, 6154972)), 900913))
=> I assume, in a query against 2D data (like planet_osm_point above)
the first one (with box2d) is slightly fastest, right?
1. But then: Why BOX and not BOX2D (similar to type box2d)?
SELECT AsText(SetSRID('BOX2D(572126 5671279,1258837
6154972)'::box2d, 900913)) -- BOX2D would be more consistent than BOX
2. Why this doesn't work I can somehow understand, since BOX... is no WKT:
SELECT ST_AsText(ST_GeomFromText('BOX(572126 5671279,1258837
6154972)', 900913)) -- (I would prefer BOX2D as said above).
SELECT ST_AsText(ST_GeomFromText('BOX2D(572126 5671279,1258837
6154972)', 900913))
SELECT ST_AsText(ST_GeomFromText('BOX3D(572126 5671279,1258837
6154972)', 900913))
-- ERROR: parse error - invalid geometry
-- HINT: You must specify a valid OGC WKT geometry type such as
POINT, LINESTRING or POLYGON
Although: There obviously *exists* in PostGIS a text-to-box cast:
'BOX3D(572126 5671279,1258837 6154972)'::box3d
=> So, I would expect these to work.
3. Regarding the WKT/EWKT and geography type, this works:
SELECT ST_AsText(ST_GeomFromText('POINT(-71.06 42.28)', 4326)) -- WKT
UNION
SELECT ST_AsText(ST_GeomFromText('SRID=4326;POINT(-71.06 42.28)'))
-- EWKT => redundant to ST_GeomFromEWKT?
UNION
SELECT ST_AsText(ST_GeomFromEWKT('SRID=4326;POINT(-71.06 42.28)'))
UNION
SELECT ST_AsText(ST_GeogFromText('SRID=4326;POINT(-71.06 42.28)'))
-- EWKT => why not ST_GeogFromEWKT?
Conversely, this does't exist/work:
SELECT ST_AsText(ST_GeogFromText('POINT(-71.06 42.28)', 4326)) --
=> would be equivalent to ST_GeomFromText
SELECT ST_AsText(ST_GeogFromEWKT('SRID=4326;POINT(-71.06 42.28)'))
-- => would be equivalent to ST_GeomFromEWKT
-S.
More information about the postgis-users
mailing list