How can I create a new geometry column of points with the first value of a geometry column of polygons?

Question

Let's say I have a Spatialite database filled with polygons. I've learned on this question that I can have as many geometry columns as I want in my database. So, I'd like to find a way of getting a result where I get the first pair of lat and long from the polygon in the line to generate a new geometry column of points... I'd need something like the following:

select (unknown query) as newgeometry,* from mydatabase

In this sample, (unknown query) would be a way of creating a geometry value of points from the first values of my geometry column of polygons. Is it possible to do on SpatiLite without an overcomplicated solution?

Answer 1

It can be done in two steps.

First, add a new geometry column to you table:

SELECT AddGeometryColumn('my_table', 'my_new_geom_column', 4326,  'POINT');

Remember to change in the previous query my dummy values with the those that fit for database schema. The 4326 value is the SRID of the new geometry column, and if you don't make reprojection it must match the value of your existing geometry column.

In the second step, populate the new column with the desired values:

UPDATE 'my_table' SET
    my_new_geom_column = ST_StartPoint(ST_ExteriorRing(ST_GeometryN(geom, 1)))
;

AFAIK Spatialite does not have a function to extract an specific vertex from a Polygon, but have it to extract vertex from LineString.

I'm using ST_ExteriorRing to get the "boundary" of the Polygon, a LineString and then ST_StartPoint to get the first point of the exterior ring. But you can use also ST_PointN(geom, vertex_number) (as @user30184 explains in the comments)

But as @raylight points in the comments if the geometry is a MultiPolygon with more than one geometry ST_ExteriorRing will return NULL. It seams that ST_ExteriorRing in Spatialite only works for simple geometries:

-- With the example data provided by the OP
SELECT
    ogc_fid
    , ST_GeometryType(geometry)
    , ST_NumGeometries(geometry)
    , ST_AsText(
        ST_StartPoint(ST_ExteriorRing(geometry))
    )
FROM sample
ORDER BY 3;

4529|MULTIPOLYGON|1|POINT(-46.739131 -23.578109)
981|MULTIPOLYGON|2|

One way to avoid this is always get the "first geometry" of the collection/MultiPolygon

SELECT
    ogc_fid
    , ST_GeometryType(geometry)
    , ST_NumGeometries(geometry)
    , ST_AsText(
        ST_StartPoint(ST_ExteriorRing(ST_GeometryN(geometry, 1)))
    )
FROM sample 
ORDER BY 3;

4529|MULTIPOLYGON|1|POINT(-46.739131 -23.578109)
981|MULTIPOLYGON|2|POINT(-46.638364 -23.555674)

I'm not sure if there are more corner cases, where this approach does not really extract the first vertex of the Polygon.