How to import CSV GEOMETRYCOLLECTION to PostGis?

In my PostgreSQL 9.4.26 (Greenplum Database 6.25.1 build) I like to import a csv with a location written as GEOMETRYCOLLECTION. What is the best practice to import this csv?

Table:

CREATE TABLE geometries (name varchar, geom geometry);

In PGAdmin I can import the script below:

INSERT INTO geometries VALUES

('Point', 'POINT(0 0)'),

('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),

('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),

('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),

('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');

select geoviewer How can I import the attached csv with fme to this table?

csvl For example:

id,location

test4,"GEOMETRYCOLLECTION(LINESTRING(4.79331731796265 51.5768852233887,4.77598190307617 51.5717544555664))"

Page 1 / 1

Hi @fbrws ,

Try using a GeometryReplacer after reading the CSV file with a CSV Reader. The geometry in your sample csv file is encoded in OGC Well Known Text which you can specify in the GeometryReplacer's parameters:

Once you have replaced the geometry, you can connect a PostGIS writer to the canvas and you should be all set. Here's an example output in PGAdmin for the sample file you provided:

Last thing to note, you'll likely want to use either a CoordinateSystemSetter before the writer or specify the coordinate system in the writer parameters as the GeometryReplacer will simply create the geometry.

Tested this morning, it worked almost perfect.

BUT ..... as I checked the locations on the map they are laying somewhere in the Indian Ocean. It should be somewhere in the Netherlands. It seems the replacer is swapping latitude for longitude and vice versa. Is there a way to correct that?

I tried using the Reprojector and CoordinateSystemSetter but that only seems to work if I dont connect it to the writer. If I do I get this error:

"Coordinate system conversion during translation is not possible"

In the Reprojector I use blank for Source C.S. and "LL84" Destination C.S.

In the CoordinateSystemSetter I use as Coordinate System LL84.

Tested this morning, it worked almost perfect.

I tried using the Reprojector and CoordinateSystemSetter but that only seems to work if I dont connect it to the writer. If I do I get this error:

"Coordinate system conversion during translation is not possible"

In the Reprojector I use blank for Source C.S. and "LL84" Destination C.S.

In the CoordinateSystemSetter I use as Coordinate System LL84.

If the problem is only that the coordinates are swapped then you can fix this using the CoordinateSwapper transformer.

Got it working now. My presumption that the latitude was swapped for the longitude and vice versa was wrong. I only had to set the writer coordinate system to LL84 and use a CoordinateSystemSetter or a Reprojector, both work.

1_fme 2_writer 3_reprojector 4_coordinatesystemsetter 5_geometr