Is layer_by_sql in ogr plugin working? #1029

Closed
goododd opened this Issue Jan 14, 2012 · 21 comments

4 participants

@goododd

Sorry for double posting to the Google group. I had a very small amount of data in ms sql server and was able to render image if I do Parameter name="layer" planet_osm_line (sorry, it should be in xml)

I'd like to create a layer based on a sql query as follows, (xml format avoided as it is not allowed in the edit box)

Datasource
Parameter name="type" ogr
Parameter name="layer_by_sql" select * from planet_osm_line
Parameter name="string" some connection string to mssql server

I tried the same sql using ogrinfo and it works. However, mapnik didn't complain anything but the image is empty. I tried to make a invalid select above to show that the ogr plugin is actually called (with some errors).

Any hints? Thanks.

@springmeyer
Mapnik member

The layer_by_sql is very new. I've personally not had a chance to test it yet. I would advise trying to get it working for some very simple case like a small shapefile.

@goododd

My sql profiler shows the sql query reached the server but somehow the returned data is not considered as a layer. No error complained. BTW, I am using the windows binaries RC. So maybe I should wait for the 2.1 release? Thanks.

@springmeyer
Mapnik member

If seen this type of behavior when the geometry type is not proper OGC WKB. Basically features are parsed but geometries are empty. Could this be the case?

@jbrwn

Was there every a resolution to this? I am seeing the same exact behavior in the windows 2.2 and 2.3 sdks (http://mapnik.s3.amazonaws.com/dist/dev/mapnik-v2.3.0.7z).

layer renders as expected with the following mapping xml:

  <Parameter name="type">ogr</Parameter>
  <Parameter name="string">MSSQL:server=.\SQLEXPRESS;database=gis;trusted_connection=yes;</Parameter>
  <Parameter name="layer_by_index">0</Parameter>

But as soon as i use the layer_by_sql parameter the layer fails to render. I can even see the sql statement getting executed on the SQL Server instance just like @goododd :

  <Parameter name="type">ogr</Parameter>
  <Parameter name="string">MSSQL:server=.\SQLEXPRESS;database=gis;trusted_connection=yes;</Parameter>
  <Parameter name="layer_by_sql>SELECT ogr_fid, ogr_geometry FROM dbo.land</Parameter>

dbo.land was imported from a shapefile via ogr2ogr. The shapefile can be successfully rendered by mapnik with the ogr driver. It can even be rendered using the layer_by_sql option:

  <Parameter name="string">D:\TileCook\TileCook.Web\App_Data\Config\land.shp</Parameter>
  <Parameter name="layer_by_sql">SELECT * from land</Parameter>

I suspected it might have something to do with mssql ogr driver but i cant pinpoint what is going on. ogrinfo has no problem reading the mssql features. I tried creating a vrt file to force an OGR layer envelope and feature count:

<OGRVRTLayer name="test">
  <SrcDataSource>MSSQL:server=.\SQLEXPRESS;database=gis;trusted_connection=yes;</SrcDataSource> 
  <SrcSQL>SELECT ogr_fid, ogr_geometry FROM dbo.land</SrcSQL>
  <ExtentXMin>-20037508.342789255</ExtentXMin>
  <ExtentYMin>-20037508.342789255</ExtentYMin>
  <ExtentXMax>20037508.342789255</ExtentXMax>
  <ExtentYMax>20037508.342789255</ExtentYMax>
  <FeatureCount>500</FeatureCount>
</OGRVRTLayer>

Still did not work. Lastly, i tried to troubleshoot things on the mapnik end but did not get very far.

//get mssql layer
    mapnik::layer lay =_map->getLayer(0);
    mapnik::datasource_ptr ds = lay.datasource();
    mapnik::box2d<double> layExt = ds->envelope();
    mapnik::query q(layExt, 1.0);
    mapnik::featureset_ptr fs = ds->features(q);
    mapnik::feature_ptr feat = fs->next();
    while (feat) {feat = fs->next();}

I would expect the while loop to iterate for as many records are returned by the SQL statement. However, the code never enters the loop. One little nugget i noticed is that layExt is (0,0,0,0) unless i force an OGR layer extent using the vrt layer as mentioned above.

I'd really like to get a solution on this. It could be a big win for windows integration. I'm up for debugging this further. I just feel i have hit a wall at the moment.

@BergWerkGIS
Mapnik member

@kernelsanders I was able to compile your NET-Mapnik 😄 and I'm also able to reproduce the problem on Windows with SQL Server.
Will take a look at it during the coming week.

@jbrwn

Sounds good. Thanks for jumping on this issue. Glad you were able to compile NET-Mapnik. I was also able to reproduce this issue using tilemill/cartoCSS.

As a workaround, i created a database view that contains the SQL logic. When i reference the view using the "layer" parameter i can successfully render the layer with mapnik. To me, this seems to rule out data issues. I feel like there is something funky going on when OGR hands off an OGRLayer object created via exeuctesql or something like that.

Thanks again!

@springmeyer
Mapnik member
@BergWerkGIS
Mapnik member

As far as I can see there is no spatial filter: OGRGeometry* spatial_filter = nullptr;

My findings so far:
I've replicated the plugin code in C# (was easier and faster for me 😏 ).
layer_by_sql works and gets the requested features, except that the extent reported is 0/0 0/0 so I think the problem is further up the line, when the extent gets evaluated.

ogr-datasource-executesql

Ogr.RegisterAll();
DataSource ds = Ogr.Open( @"MSSQL:server=.\sqlexpress2012;database=netmapnik;trusted_connection=yes;Tables=countries", 0 );
Driver drv = ds.GetDriver();
if (null == drv) {
    Console.WriteLine( "cannot get driver" );
    Environment.Exit( 1 );
}

int lyrCnt = ds.GetLayerCount();
for (int i = 0; i < lyrCnt; i++) {
    Layer lyrByIdx = ds.GetLayerByIndex( i );
    Envelope ext = new Envelope();
    lyrByIdx.GetExtent( ext, 1 );
    FeatureDefn def = lyrByIdx.GetLayerDefn();
    Console.WriteLine( string.Format(
        "---- By Index ----{0}Name:{1}{0}FeatCnt:{2}{0}Ext:{3}/{4} {5}/{6}{0}"
        , Environment.NewLine
        , def.GetName()
        , lyrByIdx.GetFeatureCount( 1 )
        , ext.MinX
        , ext.MinY
        , ext.MaxX
        , ext.MaxY
    ) );
}

Layer lyrBySql = ds.ExecuteSQL( "SELECT ogr_fid, ogr_geometry FROM countries", null, null );
Envelope extBySql = new Envelope();
lyrBySql.GetExtent( extBySql, 1 );
FeatureDefn defBySql = lyrBySql.GetLayerDefn();
Console.WriteLine( string.Format(
    "---- By SQL ----{0}Name:{1}{0}FeatCnt:{2}{0}Ext:{3}/{4} {5}/{6}{0}"
    , Environment.NewLine
    , defBySql.GetName()
    , lyrBySql.GetFeatureCount( 1 )
    , extBySql.MinX
    , extBySql.MinY
    , extBySql.MaxX
    , extBySql.MaxY
) );

Console.WriteLine( "Features by SQL:" );
Feature f;
while (null != (f = lyrBySql.GetNextFeature())) {
    Envelope fExt = new Envelope();
    Geometry geom = f.GetGeometryRef();
    geom.GetEnvelope( fExt );
    Console.WriteLine( string.Format(
        "[{0}]: {1:0.000}/{2:0.000}\t{3:0.000}/{4:0.000}"
        , f.GetFID()
        , fExt.MinX
        , fExt.MinY
        , fExt.MaxX
        , fExt.MaxY
    ) );
}
@BergWerkGIS
Mapnik member

It looks like, this is not implemented in SQL Server:
"SQL Server Spatial follows OGC closely and therefore does not implement this functionality. Neither it has implemented it as a so-called extended geometry method."
http://barendgehrels.blogspot.co.at/2011/04/extent-of-sql-server-spatial-table.html

@jbrwn

GetExtent with SELECT statement is not implemented in OGR:

This was my finding as well. That is why i tried using vrt layer which allows you to explicitly define an extent:

<OGRVRTLayer name="test">
  <SrcDataSource>MSSQL:server=.\SQLEXPRESS;database=gis;trusted_connection=yes;</SrcDataSource> 
  <SrcSQL>SELECT ogr_fid, ogr_geometry FROM dbo.land</SrcSQL>
  <ExtentXMin>-20037508.342789255</ExtentXMin>
  <ExtentYMin>-20037508.342789255</ExtentYMin>
  <ExtentXMax>20037508.342789255</ExtentXMax>
  <ExtentYMax>20037508.342789255</ExtentYMax>
  <FeatureCount>500</FeatureCount>
</OGRVRTLayer>

The extent option is only available at GDAL >= v1.10. I had to pull down mapnik v2.3.0 to test this feature since v2.2.0 uses an older GDAL version i think. When i run the mapnik code below with the vrt layer i successfully get an extent back from "ds->envelope()" call. This is where i got tripped up. It appears that mapnik is successfully reading an extent from the OGRLayer object. However, I still can't get features to render.

//get mssql layer
    mapnik::layer lay =_map->getLayer(0);
    mapnik::datasource_ptr ds = lay.datasource();
    mapnik::box2d<double> layExt = ds->envelope();
    mapnik::query q(layExt, 1.0);
    mapnik::featureset_ptr fs = ds->features(q);
    mapnik::feature_ptr feat = fs->next();
    while (feat) {feat = fs->next();}
@springmeyer
Mapnik member

Great findings. I've created #2260 to track fixing the handling of failed extents (this will avoid needing the VRT workaround). @BergWerkGIS - interesting that spatial filters are not implemented too. What I wonders is if commenting these lines might fix things: https://github.com/mapnik/mapnik/blob/cbdd112223b42576da403db9f19dc984217ea331/plugins/input/ogr/ogr_featureset.cpp#L58-L61.

@kernelsanders btw, would you be interested in a native MSSQL Mapnik plugin? Others have contacted me requesting this and I'm looking for development help to get it done.

@BergWerkGIS
Mapnik member

@springmeyer don't know if commenting these lines will help. ATM I don't have an environment to debug mapnik (maybe this will be easier with the gyp version)?
Probably checking the extent after getting the data (layer_by_sql) and setting it manually by iterating the features, if there are any, might be a temporary workaround till we find a solution with better performance.

@jbrwn

@kernelsanders btw, would you be interested in a native MSSQL Mapnik plugin? Others have contacted me requesting this and I'm looking for development help to get it done.

Yes, a MSSQL plugin would be awesome. What kind of resources do you need to get this done?

@springmeyer
Mapnik member

What kind of resources do you need to get this done?

A developer with the ambition to tackle it. I don't have time, but I could lightly advise someone on how Mapnik datasource plugins work.

@springmeyer
Mapnik member
@springmeyer
Mapnik member

@kernelsanders recapping where we are here. Does this sound right?

  • One clear problem is that GetExtent was not working, but now that you added support for manual extents (#2260) the VRT or table view workaround should no longer be needed.
  • However, still something else is preventing data from getting rendered. It may be the spatial filter is still not working (it definitely was not before if given an extent of 0,0,0,0) but it could also be something else (maybe http://trac.osgeo.org/gdal/ticket/5474).
@jbrwn

One clear problem is that GetExtent was not working, but now that you added support for manual extents (#2260) the VRT or table view workaround should no longer be needed.

Yes, agreed. This workaround is no longer needed.

However, still something else is preventing data from getting rendered. It may be the spatial filter is still not working (it definitely was not before if given an extent of 0,0,0,0) but it could also be something else (maybe http://trac.osgeo.org/gdal/ticket/5474).

I'm not sure about this. I ran a few quick tests and layer_by_sql seems to be working as expected. I'll test further and report back with my results.

@jbrwn

@springmeyer

layer_by_sql appears to be working properly for my use case (mssql) now that the optional extent parameter has been added in #2260.

One small gotcha with the mssqlspatial OGR drives is that either a geometry_columns metadata table must be present or tables and geometry columns must be specified in the connection string (e.g., Tables=schema1.table1(geometry column1)). If neither of these are present OGR will return null geometries (GetGeometryRef() == null) and mapnik will return an empty feature_ptr.

https://github.com/mapnik/mapnik/blob/master/plugins/input/ogr/ogr_featureset.cpp#L101-L112

While this can make it look like mapnik is failing to render a layer I don't consider this a mapnik issue. The OGR docs clearly spell out these requirements for the mssqlspatial driver.

From my end, this issue is resolved.

@springmeyer
Mapnik member

Fantastic @kernelsanders - thanks for testing and providing the low-down. Would you be able to document a working config/connection string over at https://github.com/mapnik/mapnik/wiki/OGR. There has obviously been some confusion around this (http://gis.stackexchange.com/questions/13199/can-mapnik-render-spatial-data-from-sql-server) so fantastic job getting to the bottom of it!

@jbrwn

Will do on the documentation.

I also wanted to add some more information here in case anyone else is interested in this issue. Apparently, the mssqlspatial OGR driver (as well as several other RDBMS drivers) passes all sql commands (ExecuteSQL()) to the database backed unaltered AND THEN applies any spatial filters in the OGR driver itself. So even though mapnik sets a spatial filter, the backed database still evaluates the "layer_by_sql" statement unaltered without any bounding box info attached to it. This is not true for tables or views specified with the "layer" parameter. For example:

<Parameter name="layer">planet_osm_point</Parameter>

is expanded by OGR to include a spatial filter and sent to the database backend as:

SELECT * FROM planet_osm_point WHERE way.STIntersects(geometry::STGeomFromText('POLYGON((0 0,1000 0,1000 1000,0 1000,0 0))',0)) = 1

In contrast, there is no such expansion when using layer_by_sql.

@jbrwn jbrwn referenced this issue in mapnik/node-mapnik Mar 25, 2015
Closed

Using MSSQL as an OGR Datasource does not work #424

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment