SQL query multipolygon by extent

[mcasamayorpolo]

There is an issue with the multipolygons that have seen queried by extent. I send several request to a Feature Service that has a feature with the following geometry:

{ "type": "Feature", "properties": {}, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ -6.036606316999747, 37.55161877899881 ], [ -6.040294098999733, 37.550431932998805 ], [ -6.040278736999732, 37.55040174999877 ], [ -6.0409003659997556, 37.55019055999883 ], [ -6.040865173999714, 37.54992747699876 ], [ -6.0366140779997535, 37.55118875599877 ], [ -6.036606316999747, 37.55161877899881 ] ] ], [ [ [ -6.091888218999736, 37.51585616999879 ], [ -6.09190863199975, 37.51589568599881 ], [ -6.091005979999714, 37.5162115999988 ], [ -6.090931076999731, 37.516192410998805 ], [ -6.089918623999743, 37.516548034998806 ], [ -6.087824955999701, 37.52178165599881 ], [ -6.087700430999711, 37.52207859599882 ], [ -6.0874047969997145, 37.52261566499879 ], [ -6.086941034999733, 37.5230609529988 ], [ -6.086680157999717, 37.5232653779988 ], [ -6.0857978379997, 37.5238632669988 ], [ -6.084206721999713, 37.52569010099881 ], [ -6.084318789999735, 37.52558969899882 ], [ -6.0844140189997455, 37.52555565399881 ], [ -6.084398110999722, 37.52563860399878 ], [ -6.084443239999722, 37.525714416998795 ], [ -6.0848871589997415, 37.52597056199878 ], [ -6.085388619999742, 37.526340939998796 ], [ -6.0859500029997315, 37.52689570699877 ], [ -6.086298270999713, 37.52737997099879 ], [ -6.086647281999738, 37.528089243998785 ], [ -6.0873193189997545, 37.52915573899881 ], [ -6.087651140999718, 37.52937126299881 ], [ -6.087770977999718, 37.529504333998815 ], [ -6.08844122899975, 37.529884991998784 ], [ -6.0891917999997185, 37.53045784199883 ], [ -6.089881516999718, 37.53111793599877 ], [ -6.0904277929997415, 37.531225031998794 ], [ -6.0906532979997445, 37.53116950499876 ], [ -6.090694995999704, 37.531199812998786 ], [ -6.090415828999731, 37.53151514399881 ], [ -6.09067768699972, 37.53188048899878 ], [ -6.090966719999719, 37.53196609699881 ], [ -6.091521925999721, 37.532329664998805 ], [ -6.093218060999752, 37.53304636399882 ], [ -6.093596545999752, 37.53313047599879 ], [ -6.093764894999729, 37.53308714099879 ], [ -6.094055539999737, 37.533067361998775 ], [ -6.094546760999742, 37.5328824909988 ], [ -6.094845578999716, 37.5328417369988 ], [ -6.095289559999706, 37.53284176899881 ], [ -6.096608454999705, 37.53302940499877 ], [ -6.097090703999719, 37.53306091799877 ], [ -6.097383570999702, 37.53303649599881 ], [ -6.098089090999699, 37.532921311998756 ], [ -6.099411486999734, 37.53261910499876 ], [ -6.100133647999735, 37.53238979299879 ], [ -6.10088261999973, 37.53205535499879 ], [ -6.1013137579997405, 37.531945749998755 ], [ -6.101522252999723, 37.531923212998805 ], [ -6.102096005999749, 37.5319963299988 ], [ -6.102310230999727, 37.53208863299878 ], [ -6.103045126999709, 37.5325116159988 ], [ -6.103352432999736, 37.53260791899877 ], [ -6.103907420999712, 37.532712913998786 ], [ -6.103489459999728, 37.53122285799877 ], [ -6.103166414999712, 37.530583467998824 ], [ -6.102954171999747, 37.5302414439988 ], [ -6.10207979999975, 37.52921079299879 ], [ -6.101399276999722, 37.528635148998816 ], [ -6.100687323999714, 37.528114685998766 ], [ -6.100385025999706, 37.527676643998774 ], [ -6.100184668999724, 37.52707504599877 ], [ -6.100105669999741, 37.5266468169988 ], [ -6.100161432999719, 37.525610901998775 ], [ -6.1001322979997425, 37.52542844699881 ], [ -6.100052876999733, 37.52528259499881 ], [ -6.099880343999722, 37.52514553799879 ], [ -6.099533706999715, 37.524946894998806 ], [ -6.098741162999737, 37.52456878399878 ], [ -6.097975828999743, 37.524329294998815 ], [ -6.097278199999741, 37.524062357998766 ], [ -6.096703207999745, 37.52379843799883 ], [ -6.096299379999743, 37.52330436099877 ], [ -6.095616806999736, 37.522072855998815 ], [ -6.095364507999725, 37.52145852799878 ], [ -6.095213267999746, 37.5212221509988 ], [ -6.09508311599975, 37.521150646998784 ], [ -6.094887627999718, 37.5210983489988 ], [ -6.093879914999739, 37.52094025999882 ], [ -6.093575939999731, 37.52084419999878 ], [ -6.093387986999709, 37.52070278899876 ], [ -6.09310511699973, 37.52036621799877 ], [ -6.092400481999755, 37.51998410899879 ], [ -6.092135987999733, 37.519806659998814 ], [ -6.091928843999732, 37.5196177389988 ], [ -6.0917072479997145, 37.5193290749988 ], [ -6.091541455999754, 37.518990248998804 ], [ -6.091507250999734, 37.51883406799878 ], [ -6.0915753179997045, 37.51857299999879 ], [ -6.091708121999737, 37.51833722599881 ], [ -6.091956259999755, 37.518111681998775 ], [ -6.09211117899971, 37.518022964998785 ], [ -6.0928947869997145, 37.51773736599879 ], [ -6.093135414999743, 37.517570923998775 ], [ -6.093308337999723, 37.51729243499878 ], [ -6.093475069999748, 37.51672753399877 ], [ -6.093918644999711, 37.516400658998776 ], [ -6.094115637999743, 37.516189529998805 ], [ -6.094402549999721, 37.51577481599882 ], [ -6.094478943999739, 37.51552034899878 ], [ -6.094471336999753, 37.515314092998835 ], [ -6.09436581599973, 37.515118396998766 ], [ -6.093786514999715, 37.514679105998795 ], [ -6.093474111999703, 37.51432288199877 ], [ -6.09344051099972, 37.514226991998825 ], [ -6.093422751999718, 37.51364438699879 ], [ -6.093238320999739, 37.51308353799878 ], [ -6.0928849949997, 37.51216189199877 ], [ -6.092808942999703, 37.51208144599882 ], [ -6.092695240999738, 37.5120470179988 ], [ -6.092578117999742, 37.512048272998776 ], [ -6.091928374999746, 37.512179758998776 ], [ -6.091566517999717, 37.51228464199876 ], [ -6.090742816999715, 37.512593303998784 ], [ -6.0904687039997265, 37.51259997199881 ], [ -6.09008997499975, 37.512551072998775 ], [ -6.090211613999735, 37.512610330998804 ], [ -6.091888218999736, 37.51585616999879 ] ] ] ] } }

One of the queries has the following parameters:

• returnGeometry: true
• spatialRel: esriSpatialRelIntersects
• maxAllowableOffset: 19
• geometry:{"xmin":-684875.7734609097,"ymin":4510396.165072259,"xmax":-675091.8338404223,"ymax":4520180.104692748,"spatialReference":{"wkid":102100}}
• geometryType: esriGeometryEnvelope
• inSR: 102100
• outFields: *
• outSR: 102100

Another query has the following parameters:

• returnGeometry: true
• spatialRel: esriSpatialRelIntersects
• maxAllowableOffset: 19
• geometry: {"xmin":-675091.8338404223,"ymin":4510396.165072259,"xmax":-665307.8942199312,"ymax":4520180.104692748,"spatialReference":{"wkid":102100}}
• geometryType: esriGeometryEnvelope
• inSR: 102100
• outFields: *
• outSR: 102100

As you can see in the following image, the feature is a multipolygon of two parts and it's inside the extents, however the result of the query is empty.

How can I fix this issue?

Thank you in advance for your help. :)

[rgwozdz]

Hello @mcasamayorpolo - using the debugger, it looks like this might be caused by a bug in one of the underlying dependencies; specifcally, terraformer/spatial. It looks like it is not properly handling multipolygon filters. I'll attempt testing the library directly and see what I can find.

[rgwozdz]

@mcasamayorpolo, I have more information. I think this is the expected behavior. You have to use a different spatialRel value. try it with esriSpatialRelEnvelopeIntersects.

[mcasamayorpolo]

Yes, I saw that if I used the parameter spatialRel = esriSpatialRelEnvelopeIntersects, it worked. However, I would need to use the parameter the spatial = esriSpatialRelIntersects.

[rgwozdz]

However, I would need to use the parameter the spatial = esriSpatialRelIntersects.

I don't think that is going to work for this data with those geometry filters. That's because neither of the geometry filters actually intersects either of the component polygons that make your multipolygon. If you adjust your geometry filter so that one side of the box goes through at least one of the component polygons, then I think you query will return data.

Can you change your data from multipolygon to polygon? If you could do that, then esriSpatialRelIntersects will do what I think you are hoping it will do.

[mcasamayorpolo]

No, I can't change my data because I would need to work with multipolygon.

[rgwozdz]

Then I'm pretty sure there is no existing operation that supports what you want to do. I'm also pretty sure ArcGIS server/clients don't recognize multipolygon geometry; see here, it's not a listed geometry-type. If you can't change your data, and you can't change spatialRel type, you may be stuck. Since FeatureServer is an approximation of ArcGIS, I think it would be wrong to introduce novel functionality of esriSpatialRelIntersects on an unsupported geometry type.

[mcasamayorpolo]

As you have said to me, I have checked a service hosted in ArcGIS REST API which has multipolygons.

If I view the Definition Service I can see that the geometry Type is "esriGeometryPolygon" as shown in the documentation https://developers.arcgis.com/documentation/common-data-types/geometry-objects.htm

However if I query the GEOJSON data of the multipolygon feature at issue, I obtain the following structure which matches with my data'structure previously sent.

As you can see the type is "MultiPolygon" and the structure is the same.

On the other hand I can't change the spatialRel, because I use the value spatialRel: esriSpatialRelIntersects to get the features inside a specific tile and the spatialRel: esriSpatialRelEnvelopeIntersects wouldn't obtain exactly the same data.

[rgwozdz]

Ok, thank you for the additional information. Can you share the URL for the ArcGIS service? For this ArcGIS service, does the query you are trying to do with the noted geometry filter return any features? We try our best to match the capabilities of ArcGIS server, so if we have a difference in results for a given query, I'll do my best to make an update. But it helps to a have a service that demonstrates as the docs are not always clear.

[mcasamayorpolo]

Ok, thank you so much. The service is the following:

https://services1.arcgis.com/nCKYwcSONQTkPA4K/ArcGIS/rest/services/Prov/FeatureServer/0

The query is the following:

https://services1.arcgis.com/nCKYwcSONQTkPA4K/arcgis/rest/services/Prov/FeatureServer/0/query?where=COD_PROV+%3D+%2707%27&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&relationParam=&returnGeodetic=false&outFields=&returnGeometry=true&returnCentroid=false&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&defaultSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=pgeojson&token=

[rgwozdz]

@mcasamayorpolo, in the query you note above, there is no geometry filter defined. Can you update the query so that it contains a geometry envelope that you plan to use?

[mcasamayorpolo]

Yes, if you use the geometry filter as the following you will get the feature:

{"xmin":-0.000002983957529067993,"ymin":4696291.017842988,"xmax":313086.0678530149,"ymax":5009377.085698988,"spatialReference":{"wkid":102100,"latestWkid":3857}}

https://services1.arcgis.com/nCKYwcSONQTkPA4K/ArcGIS/rest/services/Prov/FeatureServer/0/query?where=COD_PROV+%3D+%2707%27&objectIds=&time=&geometry=%7B%22xmin%22%3A-0.000002983957529067993%2C%22ymin%22%3A4696291.017842988%2C%22xmax%22%3A313086.0678530149%2C%22ymax%22%3A5009377.085698988%2C%22spatialReference%22%3A%7B%22wkid%22%3A102100%2C%22latestWkid%22%3A3857%7D%7D&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&resultType=none&distance=0.0&units=esriSRUnit_Meter&relationParam=&returnGeodetic=false&outFields=&returnGeometry=true&returnCentroid=false&featureEncoding=esriDefault&multipatchOption=xyFootprint&maxAllowableOffset=&geometryPrecision=&outSR=&defaultSR=&datumTransformation=&applyVCSProjection=false&returnIdsOnly=false&returnUniqueIdsOnly=false&returnCountOnly=false&returnExtentOnly=false&returnQueryGeometry=false&returnDistinctValues=false&cacheHint=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&having=&resultOffset=&resultRecordCount=&returnZ=false&returnM=false&returnExceededLimitFeatures=true&quantizationParameters=&sqlFormat=none&f=pgeojson&token=

[rgwozdz]

@mcasamayorpolo - thank you for sending this. I'm updating this since consulting with some colleagues here at Esri (geometry filters don't always work quite as expected unless you pass them in the service's native spatial reference).

[rgwozdz]

@mcasamayorpolo, I think you've identified a bug or limitation with the underlying geometry filtering dependency. I will open a ticket there.

[mcasamayorpolo]

Ok, thank you!

[rgwozdz]

@mcasamayorpolo - PR is up here: https://github.com/terraformer-js/terraformer/pull/86

[rgwozdz]

@mcasamayorpolo - latest versions of koop-core, featureserver, and winnow should fix this now.

[mcasamayorpolo]

Hi! I have upgrade the latest versions of terraformer (version 2.1.2), koop-core (version 5.0.0), featureserver (version 4.0.1) and winnow (version 2.5.2) and it doesn't work. Have I to do this in a specific order? Thank you so much.