Question 114953 in PostgreSQL Question posted in
The official documentation can be found here.

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

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?
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search