Spectrum Spatial (SSA/LIM)

Expand all | Collapse all

Use coordinates with feature service SQL query

  • 1.  Use coordinates with feature service SQL query

    Posted 04-29-2020 16:28
    Hi!

    Is it possible to use feature geometry filed in featureService.searchBySql where clause and therefore construct spatial query in SSA? For example, is it possible to get only these points, which coordinates are smaller than some variable. Something like that: this.featureService.searchBySql({sql:"Select * from \"TABLE\" where feature_x<var_x"}).subscribe((data) =>... });
    As this service I'm working with should work with every layer client adds to this service, I can't use Enterprise Designer REST for that. And as layers are too big to load all data to browser cache to filter objects after Feature Service request, I need some method to spatially query Spectrum tabels straight from SSA.

    Thanks in advance,


    ------------------------------
    Ott Koik
    Ruumab OÛ
    Tartu
    ------------------------------


  • 2.  RE: Use coordinates with feature service SQL query

    Posted 06-04-2020 10:55
    I finally had time to figure this out and maybe someone else will have this question. Therefore, I will share it in here as well.
    The main thing was, I didn't know I have to use WKT geometry in MISQL query with feature service. For example, to find polygons that intersects with point, SQL would be following: select * from \"/path/to/NamedTables/table\" where MI_Intersects(geometry_column, FromWKT('POINT(x_coordinate y_coordinate)','epsg:3301')). And now I can use layer name and x/y coordinates as variables in my extensibility component and therefore, my problem is solved.

    I hope this will be helpful for someone in the future,

    ------------------------------
    Ott Koik
    Ruumab OÛ
    Tartu
    ------------------------------



  • 3.  RE: Use coordinates with feature service SQL query

    Employee
    Posted 06-05-2020 16:42
    Ott,
    Sorry ​for the delay in the response.
    Using the feature service allows you to describe a geometry as input in a number of different ways one of which is WKT.
    For a point, you can use the MI_Point function like this:
    select Country,Capital from "/Samples/NamedTables/WorldTable" where MI_Point(-100, 40, 'epsg:4326') within obj
    or you could use the From_WKT, From_WKB , From_GeoJSON or FromKML functions that each have unique text syntax but result in the same thing; a Spatial geometry.
    There are some examples here:
    http://support.pb.com/help/spectrum/19.1/en/webhelp/Spatial/index.html#Spatial/source/misql/misqlapiguide/functions/geometryconstructor.html
    Unless you have data being fed to you in those formats, the easiest one for hand creation is MI_Point, MI_Line and MI_Polygon
    Let us all know if you have any problems with this.

    ------------------------------
    Eric Blasenheim
    Spectrum Spatial Technical Product Manager
    Troy, NY
    ------------------------------



  • 4.  RE: Use coordinates with feature service SQL query

    Posted 06-08-2020 06:45
    Hi!

    Thanks for your answer. MI_Intersects and MI_Box with nodes as dynamically changing coordinates worked for me.

    Regards,

    ------------------------------
    Ott Koik
    Ruumab OÛ
    Tartu
    ------------------------------