Dear people,
I'm testing Mapsuite 3.0 and run into the apparent lack of schema support for PostGIS. Did I miss something or is this really lacking? For us schema support is a hard requirement, as we deal with lots of data in one enterprise geodatabase, divided in many schema's.
With kind regards,
Wouter
Wouter,
I want to make sure I understand the issue. To use a schema to my understanding you need to add the schema name to the tables as you use them. For example Accounting’s schema might be Accounting.Users if you wanted to access the user table for accounting correct? I want to make sure you tried when specifying the table name in the constructor for the layer you passed in “accounting.user” as the table and it did not work? Did you get an exception, and if so can you pass it along to me? I know we do some special query and maybe they are blowing up when we tack on the schema name.
There are two things we can do for this. The first is based on if this generates an error we can fix whatever schema issues there are. There is also another way you can try. Whenever we execute an SQL statement we raise an event on the feature source called OnExecutingSqlStatement. If you catch that event you can see the exact SQL that we will execute. You can also modify it as you see fit. Maybe if you want you can play around with that as we try and re-create this. I want to first make sure you tried passing in the table fully qualified.
On way or another we will be able to add schema support it, we just have not had anyone ask for it.
David
David,
To help you with my question regarding the PostGIS schema support, I’ll give an extensive explanation on how to deal with schema’s. It’s quite important, as there are potentially dangerous mistakes in the catalog querying, both for the PostgreSQL catalogs (pg_catalog), and for the geometry_columns table (the PostGIS catalog).
I got my information from the PostgreSLQ server logging, which I set to ‘log all’.
Fortunately, the bugfixes are amazingly easy (parse the tablename in the connection string, and add a line or 2 to the postgres table and catalog querying).
The following codes SEEMS to works perfectly well, and is not using schema support, but there’s still a problem, which will be explained later.
Dim lyrovl As New LayerOverlay
Dim connectString As String = “Server=172.16.192.1;User Id=wouterb;Password=Ceret1975;DataBase=gdb;”
Dim postcode As New PostgreSqlFeatureLayer(connectString, “adm_pc4_2008”, “gid”, 28992)
postcode.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1
postcode.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20
lyrovl.Layers.Add(postcode)
WinformsMap1.MapUnit = GeographyUnit.Meter
WinformsMap1.Overlays.Add(lyrovl)
WinformsMap1.CurrentExtent = New RectangleShape(10000, 640000, 290000, 300000)
WinformsMap1.Refresh()
In the following example I will try to read the same data, but from a specific schema (‘nl’).
Dim lyrovl As New LayerOverlay
Dim connectString As String = “Server=172.16.192.1;User Id=wouterb;Password=Ceret1975;DataBase=gdb;”
’ Schema ‘nl’
Dim postcode As New PostgreSqlFeatureLayer(connectString, “nl.adm_pc4_2008”, “gid”, 28992)
postcode.ZoomLevelSet.ZoomLevel01.DefaultAreaStyle = AreaStyles.Country1
postcode.ZoomLevelSet.ZoomLevel01.ApplyUntilZoomLevel = ApplyUntilZoomLevel.Level20
lyrovl.Layers.Add(postcode)
WinformsMap1.MapUnit = GeographyUnit.Meter
WinformsMap1.Overlays.Add(lyrovl)
WinformsMap1.CurrentExtent = New RectangleShape(10000, 640000, 290000, 300000)
WinformsMap1.Refresh()
This produces the following error:
{“Can not find the geometry column in table : ‘nl.adm_pc4_2008’”}
The explanation is quite simple:
MapSuite selects the table name from the PostGIS repository (geometry_columns), in order to determine the geometry column and the spatial reference id, but uses the fully qualified tablename, whereas the schema infomration is stored in the repository in a separate column. From the PostgreSQL server log:
select f_geometry_column,srid from geometry_columns where f_table_name=‘nl.adm_pc4_2008’
This produces an empty resultset, the correct query is:
select f_geometry_column,srid from geometry_columns where f_table_schema=‘nl’ and f_table_name=‘adm_pc4_2008’
NOTE!
It’s not only important to do this when working with multiple schema’s, but also when only the public schema is supported, because there could be more tables with the same name, from which one of them lives in the public schema. So the current approach is dangerous!
A comparable situation exists when querying the PostgreSQL pg_catalog schema, to retrieve details about the table as it is registered by the database server itself. Also here you should never ever query for columns belonging to a table without specifying the schema! Currently, the schema is NOT specified, which leads to the following behaviour:
when looking up a specific column for a specific table, you might get more then one result if the table exists in more then one schema. MapSuite queries for the geometry column, and in my (test)case, gets two results, for one table. Query, taken from the server log:
select pg_attribute.attname, pg_type.typname, pg_attribute.attlen from pg_attribute,pg_class,pg_type where pg_type.typname=‘geometry’ and pg_attribute.attrelid=pg_class.oid and pg_class.relname=‘adm_pc4_2008’ and pg_attribute.atttypid=pg_type.oid and pg_attribute.attnum>0;
It happens that I do have two ‘adm_pc_2008’ tables, in different schema’s, and as a result, get the following back:
“the_geom”;“geometry”;-1
“the_geom”;“geometry”;-1
When the two results where different, which one is the one to choose? No error, but you could potentially pick the wrong one.
On the contrary, selecting the ‘nl.adm_pc4_2008’ table in this way doesn’t produce a result at all, it does not exist in the pg_class catalog (which is the table catalog of PostgreSQL). So, you would end up with another error.
The tricky thing is, that this does not give SQL errors, it just works. And when testing with only a few different tables in the public schema you’ll never notice the problem. But to be sure to select the metadata for the right table, you have to add the pg_namespace to the query, and join it in the correct manner:
select pg_attribute.attname, pg_type.typname, pg_attribute.attlen from pg_attribute,pg_class,pg_type,pg_namespace where pg_type.typname=‘geometry’ and pg_attribute.attrelid=pg_class.oid and pg_class.relname=‘adm_pc4_2008’ and pg_attribute.atttypid=pg_type.oid and pg_attribute.attnum>0
and pg_namespace.oid=pg_class.relnamespace and pg_namespace.nspname=‘nl’;
Now I get only 1 record, for the ‘nl.adm_pc4_2008’ table:
“the_geom”;“geometry”;-1
Same when dealing with data in the public schema, just replace the pg_namespace.nspname=‘nl’ by pg_namespace.nspname=‘public’.
NOTE! The principle of always adding the schema name to PostgreSQL catalogs is very important, when retrieving information from them, and also when working in the public schema.
I want to emphasize that this is a realworld scenario/nightmare, especially in a large enterprise database. For example, we use different schema’s for the same data stored in different coordinate systems, e.g.
public.zip-code could be for the zip-codes, web ready in wgs84 and generalized, whereas
nl.zip-code could contain the raw data in the dutch coordinate system!
This would return two different srid’s on the geometry_columns select, and multiple (geometry) columns when selecting the PostgreSQL catalog…
I shouldn’t have done this, this mail is worth about a thousand dollar…
With kind regards,
Wouter
Wouter,
I appreciate the time and care in your detail explanation. I had used schemas in the past without thinking about them much. I will forward this on to see what the viability and time frame of putting this in would be. I hope give you some good news and details soon.
David
Wouter,
We have made the changes to the Postgre assembly to support schemas. You can contact support@thinkgeo.com and ask for Desktop Edition build 418 eval and that will have the updates in it.
David
Wouter,
Also here is the API we added with the schemas supported.
PostgreSqlFeatureLayer(string connectionString, string tableName, string featureIdColumn, int srid, string schemaName, string geometryColumnName).
Thanks,
Ben