Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GEOPACKAGE Column id ambiguity when using BBOX #5858

Closed
DennisRutjes opened this issue Sep 9, 2019 · 2 comments

Comments

@DennisRutjes
Copy link

commented Sep 9, 2019

We are using a geo package as a data source, the layer contains the column id.
When using a bounding box, MapServer joins the geospatial index table(rtree_Perceel_geom in our case) with the layer table and produces invalid SQL due to the double "id" columns in both tables.
The resulting SQL query is shown below:

SELECT "gml_id", "id", "perceelLabelID", "identificatieLokaalID", "identificatieNamespace", "beginGeldigheid", "tijdstipRegistratie", "volgnummer", "statusHistorieCode", "statusHistorieWaarde", "kadastraleGemeenteCode", "kadastraleGemeenteWaarde", "sectie", "AKRKadastraleGemeenteCodeCode", "AKRKadastraleGemeenteCodeWaarde", "kadastraleGrootteWaarde", "soortGrootteCode", "soortGrootteWaarde", "perceelnummer", "perceelnummerRotatie", "perceelnummerVerschuivingDeltaX", "perceelnummerVerschuivingDeltaY", "perceelnummerPlaatscoordinaatX", "perceelnummerPlaatscoordinaatY", "geom" FROM "Perceel" JOIN "rtree_Perceel_geom" ms_spat_idx ON "Perceel".ROWID = ms_spat_idx.id AND ms_spat_idx.minx <= 668770.029039325 AND ms_spat_idx.maxx >= 305483.307024382 AND ms_spat_idx.miny <= 1561015.59774587 AND ms_spat_idx.maxy >= 890519.969865935

We are not in control of the manufactured geo package.
Due to the large geo package and performance reasons, we would not like a query in the DATA segment of the MapServer config file

example layer config

     LAYER
      NAME   "perceel"
      STATUS  ON
      TYPE    POLYGON

      METADATA
        "wfs_title"         "perceel"
        "wfs_abstract"      "Het op geografische wijze verschaffen van inzicht in de indeling, vorm en ligging van de kadastrale percelen, fungeert als schakel tussen terrein en registratie, vervult voor externe gebruiker vaak een referentiefunctie, een ondergrond ten opzichte waarvan de gebruiker eigen informatie kan vastleggen en presenteren."
        "wfs_keywordlist"   "Kadastrale percelen,infoMapAccessService"
        "wfs_extent"        "-25000 250000 280000 860000"
        "wfs_include_items" "all"
        "wfs_bbox_extended" "true"
        "wfs_enable_request" "*"
        "gml_featureid"     "id"
        "gml_include_items" "all"
        "gml_exclude_items" "perceelLabelID,gml_id"
        "gml_geometries"    "begrenzingPerceel"
        "gml_types"         "auto"
        "gml_geom_alias"    "begrenzingPerceel"
        "ows_metadataurl_type"     "TC211"
        "ows_metadataurl_format"   "text/plain"
        "wfs_use_default_extent_for_getfeature" "no"
      END

      CONNECTIONTYPE OGR
      PROJECTION
        "init=epsg:28992"
      END
      CONNECTION "#GPKG_LOCATION#/perceel.gpkg"
      DATA "Perceel"
      PROCESSING "NATIVE_SQL=YES"

      DUMP TRUE
      TEMPLATE void
    END

Is there a solution or workaround?

With kind regards,

Dennis Rutjes

@jratike80

This comment has been minimized.

Copy link

commented Sep 9, 2019

Could you help us by attaching a small geopackage to test with? By a quick look to the query I can't understang the problem. The join seems to use the internal perceel.ROWID column for the join and perceel.id should not have any effect.

"Perceel".ROWID = ms_spat_idx.id

@rouault

This comment has been minimized.

Copy link
Contributor

commented Sep 9, 2019

I believe the issue is the , "id", column selection which suffers from ambiguity since it is found both in Perceel and rtree_Perceel_geom. This should be qualified by "Perceel".

@rouault rouault closed this in e3c6d47 Sep 9, 2019
rouault added a commit that referenced this issue Sep 9, 2019
OGR GPKG: fix potential SELECT ambiguity for 'id' column (fixes #5858)
backporting bot pushed a commit that referenced this issue Sep 9, 2019
When using a GeoPackage table that has a 'id' column, in some circumstances,
MapServer composes internally a SELECT statement, but fails to prefix the
id column with the table name, which causes an ambiguity when joining with
the spatial index RTree table that has also a id column.
rouault added a commit that referenced this issue Sep 9, 2019
[Backport branch-7-4] OGR GPKG: fix potential SELECT ambiguity for 'id' column (fixes #5858)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
3 participants
You can’t perform that action at this time.