CODEWITHSUNDEEP
laravelleafletgispostgisgeojson
Skery
Skery

Reputation: 57

Postgis - What type to use for any geometry object

I'm trying to use some postgis functions inside a Laravel project to store some geographical information.

With Leaflet and its leaflet-draw plugin, a user can spot on a map the location of an archeological site. But the thing is he can either locate it as a point, if it's a small and precisely located site, or within a polygon that he draws on a map if it's larger or not precise. The layers drawn on a map give me a geojson that I want to transform with Postgis to a geometry object.

So I needed, for my "posha_sites" table, a geometry type that can store anyhting and it seemed to me that the GeometryCollection type was made for this, so here is the up function of my migration when I added my geometry column to my table.

public function up()
{
    Schema::table('posha_sites', function (Blueprint $table) {
        $table->geometrycollection('geometry');
    });
}

At first I tried to use njbarrett/laravel-postgis package that I found on GitHub, but I had a SRID problem when trying to insert add GeometryCollection.

$site->geometry = new GeometryCollection([new Point(-48.23456, 20.12345)]);

Which gave me this error: 

Invalid parameter value: 7 ERREUR: Geometry SRID (0) does not match column SRID (4326)

I don't know if the package offers a way to specify the SRID, so I then just tried a raw sql query using postgis functions:

update posha_sites set geometry = ST_SetSRID(ST_ASTEXT(ST_GeomFromGeoJSON(\'{"type":"Point","coordinates":[-48.23456,20.12345]}\')), 4326)

This way the SRID is set but I still get an error:

Invalid parameter value: 7 ERREUR: Geometry type (Point) does not match column type (GeometryCollection)

What should I insert so that it matches the GeometryCollection type or should I use another type ?

Upvotes: 0

Views: 2019

Answers (1)

IvanSanchez
IvanSanchez

Reputation: 19089

According to your error message...

Geometry type (Point) does not match column type (GeometryCollection)

...it seems that your PostGIS column is a GeometryCollection but your data is a Point. And indeed, you're providing a GeoJSON Point geometry to PostGIS here:

ST_GeomFromGeoJSON(\'{"type":"Point","coordinates":[-48.23456,20.12345]}\')

The approach here would be to wrap that GeoJSON Point geometry into a GeoJSON GeometryCollection geometry, e.g.:

ST_GeomFromGeoJSON(\'{"type":"GeometryCollection", "geometries": [{"type":"Point","coordinates":[-48.23456,20.12345]}]}\')

Note that a GeometryCollection can have zero, one or more geometries. The GeoJSON representation of a GeometryCollection includes an array (i.e. [], [{...}, {...}]) of geometries - the brackets surrounding the point denote it's an array of one geometry.

Another approach (depending on your requirements) would be to change the PostGIS column type from GeometryCollection to Geometry (or Geography depending on the importance of geodesy in your scenario). A Geometry PostGIS field can hold Points, LineStrings, Polygons or MultiGeometrys; whereas a MultiGeometry field can only hold a MultiGeometry value (and inside that value you can pack other geometry types).

If the last paragraph was confusing, I suggest you become acquainted with the Simple Features Specification, which explains the relationships between different geometry types.

Addendum: relevant PostGIS documentation about column types is at https://postgis.net/docs/using_postgis_dbmanagement.html

Upvotes: 2

Related Questions