postgispostgresqlspatial-databasestorage
I'm faced with the following problem. I have to migrate from Oracle database to PostgreSQL+PostGIS. Currently all geometries of all types are stored in one table and each record contains a "lid" field which indicates features of the same layer.
What are the pros and cons of using such a method? Should I break up data into multiple tables if I don't need to use the database with third-party software? What about performance of spatial queries, will the indexes help me?
The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:
Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),
Since you're are using the 4326 SRID, you can cast from geometry to geography and vice versa like this :
select st_distance(<geom_column>::geography) from myTable;
Most of the functions in postgis are overloaded, meaning, they return values and run a different procedure based on the input data type.
eg:
boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);
boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);
they have the same name, but the first function returns T/F based on units of the srid, and the second returns T/F based on meters.
If you plug into the function geog types, for its calculation, will take into account the curve of the earth so the algorithm 'costs' a bit more.
If you want to check how much more it costs, you can run for each query an EXPLAIN-ANALYSE
A solution to this issue is to cut the features in line segment with 2 vertices each with the Chopper transformer and then rebuild areas using the AreaBuilder (in case of multipolygon input this results in ambiguous id's the areas are built of, the Aggretgator transformer makes multipolygons again).
Since building areas results in loss of attributes, the attributes can be retrieved from the input features via FeatureMerger.
As a result i got straight (multi)polygons in my PostGIS (no curvepolygons anymore).
Using ArcStroker transformer is not necessary since the Chopper ist doing the segmentation on arc segments.
https://www.safe.com/transformers/chopper/
Edit:
And in addition here is another answer to the fme postgis writer issue. To prevent the writer from creating generic geometries, we have to already set the write directive 'create generic geometry column' to false when adding the writer as shown below ....
... in order to explicitly define the geometry type for each feature type:
Best Answer
If you don't need third party support and don't forsee the need to query by type keeping them in the same table works just fine. Alternatively you could use an inheritance model as discussed in chapter 3 of PostGIS in Action.
http://www.postgis.us/chapter_03_edition_1
From an architecture perspective PostGIS doesn't really care if in a query multiple different types are used. If it performed fine for you in Oracle it will be just as if not better performant in PostGIS.
There are 2 reasons to split it up (and either can be done later as needed): 1) Prevent people from inserting different types you don't want like geometry collections, circular strings and what not (which you could just manually define a constraint)
2) If you have a billion points and 1000 polygons, and do a lot of point in polygon tests, the speed is much better if when you query and do your join -- its against a billion -- to 1000 record table as opposed to a billion to billion record table. This would be the case for any spatial database I think (not specific to PostGIS). It's true for all relational queries I would guess too (not specific to spatial queries).