Is there a Geo type literal? Or only sub-type

user_651174 0
Viewed 33

Some databases support literals of the various GEO type, for example in Postgres, you can do:

SELECT POINT '1,1';

And in Postgres it enforces the sub-type at the column level:

SELECT POINT '1,1' UNION ALL SELECT LINE '0,0,1,1';

ERROR: UNION could not convert type line to point

However, some databases allow using various GEO types in the same column (such as BigQuery, as one example).

  1. Is it possible to use the GEO|GEOMETRY|GEOGRAPHY keyword itself for literal creation, for example something like SELECT GEO '1,1' FROM tbl ?
  2. Is it possible to have a GEO column that supports any sub-type within it?
postgis gis postgresql sql
2 Answers

The following code seems to do the job.
Having said that, the fact that we can do something doesn't necessarily mean we should do it.

A similar post states:

"Client applications usually deny the work with a single generic geometry type column as well as multiple geometry columns in one table!"

          select 'POINT(1 1)'::geography
union all select 'LINESTRING(0 0,1 1)'::geography
geography
0101000020E6100000000000000000F03F000000000000F03F
0102000020E61000000200000000000000000000000000000000000000000000000000F03F000000000000F03F 
          select 'POINT(1 1)'::geometry
union all select 'LINESTRING(0 0,1 1)'::geometry
geometry
0101000000000000000000F03F000000000000F03F
01020000000200000000000000000000000000000000000000000000000000F03F000000000000F03F

Fiddle

user_63364790

In addition to David's answer, the thing about Geo type in PostgreSQL, you should distinguish between

  • Old (and mostly obsolete) native PostgreSQL types like POINT and LINE in your example. These cannot be mixed, as each one is a separate SQL type.
  • Modern types provided by PostGIS extension: GEOMETRY and GEOGRAPHY.

The GEOMETRY and/or GEOGRAPHY types in most databases, including PostGIS types, try to follow OGC and SQL/MM standards and can contain any collection or mix of points/lines/polygons spatial types. Here a single SQL type (e.g. GEOMETRY) may contain arbitrary spatial type(s).

As for literals, the syntax varies. PostgreSQL supports casts using 'POINT(1 1)'::geometry syntax, but in all databases you can use equivalent functions like ST_GeomFromText('POINT(1 1)') (function names vary across DBMS) to build geometry or geography.

user_198800
Related