[postgis-users] Geometry constructors/casts with WKT, BOX and ST_GeomFromText/ST_GeogFromText

[Stefan Keller]

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.