0

I have this SQL request

select name from osm_places where 
name in (select capital_city from osm_places where capital_city != '')

Is it possible to write such a request in < ogc:Filter> section?

I try this

<ogc:Filter>
  <ogc:PropertyIsEqualTo>
    <ogc:Function name="in">
      <ogc:PropertyName>name</ogc:PropertyName>
      <ogc:PropertyName>capital_city</ogc:PropertyName>
    </ogc:Function>  
    <ogc:Literal>true</ogc:Literal>
  </ogc:PropertyIsEqualTo>  
</ogc:Filter>

But GeoServer generates this SQL query

select name from osm_places where 
    name in capital_city
1

There is currently no way to force a subquery, but you can get close using this extension module:

https://docs.geoserver.org/stable/en/user/extensions/querylayer/index.html

It will run the subquery separately, and then use the retrieved IDs for filtering. Not as efficient as a subquery, but should get the job done if the list of ids to be retrieved is not too long. Ah, the sub-table must be published as a layer, but you don't need to make it advertised.

  • Ok thank you for your answer. I think in my case it is better and faster to remake the SQL table – slr Feb 20 at 7:07
0

I don't think you can do this, while most OGC filters can be converted into SQL filters not all SQL filters can be converted into OGC filters. Especially ones that require knowledge outside the current feature.

The best that I think you can do is:

<ogc:Filter>
  <ogc:PropertyIsNotEqualTo>
    <ogc:PropertyName>capital_city</ogc:PropertyName>
    <ogc:Literal></ogc:Literal>
  </ogc:PropertyIsNotEqualTo>  
</ogc:Filter>

You will then get name back as part of the selected feature.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.