I have data stored in a SQL server database in a native geomtry type format. I would need to migrate this data to a postgis database and store the data using simple geometry (point, lines and polygons instead of the generic geometry datatype).
My data is stored as geometry but it is known and expected that specific tables will polygon or lines. Is there a somewhat simple way to do this without creating the destination tables beforehand?
Page 1 / 1
I might be missing some of the question, but in writing to PostGIS you do not need to specify the geometry data type. The PostGIS Writer with its embedded PostGIS API will do this for you.
Eg. Using an FME PostGIS Writer with Table Handling as “Create if Needed” will, within the Writer’s PostGIS API, execute something like this during the initial part of the FME write for you:
CREATE TABLE IF NOT EXISTS mytable (myfield varchar, geom geometry);
ie. FME will automatically create a new PostGIS Geometry Field for you in any newly created Table during the write.
In terms of how spatial data flow works in the FME workspace it will be:
Source data = SQL Server Geometry Data Type
→ Transformed into FME Feature Spatial Data Type by the FME SQL Server Spatial Reader. FME holds the geometry data as its own native FME spatial data type whilst processing within the workspace.
→ FME spatial data transformed into PostGIS Geometry Data Type by the FME PostGIS Writer.
Noting that PostGIS geometry data type fields will store any type of vector shapes, and does not need separate tables for points, polygons, polylines etc.: If there is however a requirement to split the data into separate Points, Lines, Areas tables, this can be done dynamically using the GeometryFilter Transformer.
The different Ports of this Eg. Point, Line, Area can then be sent to PostGIS Writer(s) that have their Writer Geometry Parameters set with additional settings to put an extra constraint on the PostGIS geometry field created for what geometry types PostGIS will allow to be inserted/updated so that during the Writers initial table creation it should execute something more like this.
CREATE TABLE IF NOT EXISTS mytable (myfield varchar, geom geometry(LINESTRING));
Thank you for your reply.
I know that you dont need to specify the geomtry type in postgis. But in my case i need to. The use of native geometry type in SQL Server is actually not correct to the specification i am working with. It is also used as a method to validate the geometry. e.g not allow to store any other geometry type than specified for the dataset.
Using GeometryFilter does not affect how the table will be created. It will still be created as geom geometry(geometryz, 4326) or similar. I need it to be geom geometry(polygon, 4326) etc
One problem is if you want to read the table from QGIS desktop. If you can store multiple geometry types in on table it becomes duplciate in the database list view.
During Writer Creation, turn off “Create Generic Spatial Columns” Parameter to allow the Writer to expose the Parameter for setting a PostGIS geometry spatial type constraint during Table Creation
Can then split the source table(s) into separate Points, Lines and Polygons Features and Schemas. Note I have set this workflow to make sure the Schema Features enter the Writer first (important step for dynamic writing)
Then the Geometry Data Type can be set by the First Feature, which because of GeometryFilter, will be either all Points for the ….._Points schema etc.
If on the other hand the source SQL Server Tables also only ever contain just a single spatial feature type per table, then this can be simplified to just.
Thats a great answer! Thank you for the details you included. Very useful, will give that a go! It looks like that would solve my Challenges. Thank you
