using postgis in local government (enterprise)

City of AshevilleInformation Technology Services

PostGIS with ArcSDE – The Power of Native Spatial Data


WhyDo you really want to continue to be one of these guys?


Why

Be cool, look cool, and do AMAZING things with PostGIS! and SDE

Or do you want to be one of these guys!


Thumbs Up Flexibility!

Makes your spatial database act like a “real” database - more normalized!

You can replicate and deploy the database in multiple places very easily, and depending on what clients you use cheaper.

You do not need ESRI Software to view, query, and edit spatial data. Yes you can use ESRI Software but it’s not necessary.

And at ArcWhatever they will call it at 10.1, you may not need SDE at all, if you are using PostGIS (yes and SQL server 2008 and Oracle Spatial also, but this is not about those minor closed source, proprietary desktop applications)!


Thumbs Up Spatial views with spatial SQL do analysis on live data and are always - as up-to-date as the source data.

You can also run ”cached” versions of the views with SQL commands when the performance of the view is inadequate.

This means no complex geo-processng model, with 35 steps, that fails for three days, after running for months. Then miraculously starts to run again on the fourth day. Of course this was after spending the past three days trying figure out why its failing.

You can do things with spatial SQL ( PostGIS even more so) that you cannot do with traditional SDE setups. (examples coming)


“Old School” SDE SetupWhat could we support?


“New School” SDE SetupWhat can we support?

View with no SDE!


Thumbs DownYes it seems that some tasks are slower in PostGIS with SDE.

Such as Large spatial selections. But…

When I copied and pasted the SQL (the SQL that SDE used) I got from a SQL trace and then ran the copied SQL from a PostGreSQL command line; the SQL took Milliseconds to complete. In SDE the same SQL commands took sometimes more than 30 seconds to complete.

Why? Not sure. ESRI tech support were able to replicate the issue. They, ESRI support, declined my request to fix the issue.


Thumbs DownTables with large numbers of records open and sort slowly in ESRI products. Again the same sort and open runs from the SQL prompt with no performance issues, only ESRI products have had an issue.

Not sure why ESRI software has issues with PostGreSQL over other RDBMS’s but its not the Database. – a conspiracy? just saying you might want to break out the tin foil!


What is a view?A view is a virtual table. Views are created by “saving” a SQL query in the database. The “saved” query is treated just as a database table.

Some advantages of views:

Views reduce data redundancy, and reduce the potential issues that come from redundant data.

The data in a view can be calculated, collated, and modified into a new abstracted arrangement without having to re-create and maintain a new table – its dynamic.

Views can join multiple tables into a single virtual table.

Views can be used to represent simplified subsets of data.

Views take very little disk space, relative to outputting the data as new redundant table.


Show Me the DataSimple Spatial View – ST_Union (Dissolve)

SDE: Must use a Geo-Process SQL Server 2008: Yes


Show Me the DataSimple Spatial View – ST_Intersect

SDE: Must use a Geo-Process.SQL Server 2008: Yes


Show Me the DataMore Complex Spatial View – st_area

SDE: Must use a Geo-Process.SQL Server 2008: Yes


Show Me the DataMore Complex Spatial View –ST_line_interpolate_point

SDE: Maybe a complex Geo-Process? SQL Server 2008: No


Show Me the DataMore Complex Spatial View – st_makepoint

SDE: Must use a Geo-Process. SQL Server 2008: Yes


Show Me the DataEven more Complex Spatial View – ST_line_interpolate_point, st_makeline

SDE: Maybe a very complex Geo-Process? SQL Server 2008: No


Show Me the DataEven more Complex Spatial View – Re-project ST_Transform

SDE: Must use a Geo-Process.SQL Server 2008: No


Show Me the DataEven more Complex Spatial View – Topological Errors ST_Dwithin, ST_Disjoint

SDE: Must use Topology and data must be in a Feature Dataset. SQL Server 2008: Maybe with extra steps


Show Me the DataCan you really do that with Spatial SQL (Not a View) – Historical Logging

SDE: Maybe a very complex Geo-Process? SQL Server 2008: Maybe with extra steps


Show Me the Data

This uses a SQL union not a spatial union to combine two (or more) feature classes into one, as a spatial view.

The source data is stored once and represented as a aggregate in one spatial view.

Then the view is added to an ESRI feature dataset so an ESRI network dataset (network analyst) can be built.

Can you really do that with a Spatial View – SQL UnionSDE: Must use a Geo-Process and the feature class must be in a feature dataset!

SQL Server 2008: No


Show Me the Data

Add the street name to a sidewalk, where the sidewalk is parallel to the street.

Add the street name to the crosswalk it crosses.

Add the park name to the sidewalk when the sidewalk(or path) is in or crosses a park.

Can you really do that with Spatial SQL – ST_Dwithin, ST_Azimuth, ST_StartPoint, ST_EndPoint, ST_IntersectSDE and SQL Server: Yea, right.


Show Me the DataOne more in the: Can you really do that with Spatial SQL Category

ST_MakeGISerAwesomeSDE and SQL Server: Not possible ever, PostGIS only!


Howdgeyoudothat?Not with Photoshop, I used GIMP which is of course – FOSS.

How to get spatial SQL view to show up in SDE.

1. Create a view using the SDE command line use an existing layer as the source. The existing table must have the same geometry data type (point, line, polygon).

2. Use a PostGresSQL command prompt to drop-then-create the view – with the appropriate SQL logic.

3. You will have to update some SDE tables. Specifically t he sde_table_registry table and update the object_flags field with the correct value (depends on setup) and the rowid_column field – usually objectid but this can be any unique id. This column should be type int and the column should be the primary key in the source table. (I script this in the SQL creation of the view

4. Set permissions.


Tips and TricksAlways cast the data in views, especially when doing some kind of calculation\aggregation with field data. SDE will guess at the data type and length when it is not cast, when SDE inserts it into the sde_column_registry table and it frequently guesses wrong. The wrong type could cause ESRI products to ignore the column and/or randomly crash.

You really need a Primary Key defined, and a primary key is required for edits. This is always good practice anyway. Call it objectid for SDE simplicity. And make sure it is of type int for best results. The primary key is not always created by default with ESRI products in PostGIS so you may have to do it.

If the source table has no primary key, you can always add a field of type serial. PostGreSQL will create an integer that is sequenced – the same thing as objectid in SDE.

Some PostGreSQL data types are not valid in some ESRI Software; text, smallint, and bigint can cause issues. The table will not always display and could cause one of those random ESRI crashes.


Tips and TricksIn spatial views, ensure that there are no duplicate ids, where the id is defined as rowid_column in the sde_table_registry table. ArcGIS products will only display one of the duplicated records without error messages. Most Open Source clients will fail opening the layer

SDE can freak out with some special characters _ are okay

SDE can freak out with spaces in table names and field names use a _ instead of a space.

SDE freaks out with capital letters in names of tables, names of views, and names of fields (in PostGreSQL).

(Interestingly, SDE capitalizes the view name in data listings and you cannot change this, I tried)


Tips and TricksSDE will not allow more than 1 spatial field and will not open the table or view if it has more than one.

You cannot rename Feature Classes in PostGreSQL with SDE

All users should have a schema in PostGreSQL and SDE. Spatial selections with ESRI software require a SDE table owned by the user in the users schema to select > 100 features. SDE will work and all functionality is fine until a user selects more than 100 features. This is the SDE threshold for using the SDE log file. In PostGreSQL the SDE log files are created in the User Schema

ArcCatalog will not update the postgis geometry_columns table with correct geometry type. ArcCatalog always fills it with a value of “geometry”. Some Open Source client software uses this table and is expecting – MULTIPOLYGON, POLYGON, MULTILINESTRING, LINESTRING LINESTRINGPOINT, or POINT, and will fail to add the table if it says “geometry”

Avoid long field names. PostGreSQL will allow SDE will not. Long field names will result in random ArcGIS crashes.


Shameless PlugFor me, the whole things is made easier and possible by one important piece of software.

And it’s Commercial – Closed Source!

Truly the best software I have ever used.


The End

using postgis in local government (enterprise)

Technology

spatial sql postgis

complex spatial view

spatial views

sql query

data simple spatial

geoprocess sql server

sql commands

copied sql