Spectrum Spatial (SSA/LIM)

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
------------------------------

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
------------------------------

Original Message:
Sent: 04-29-2020 16:28
From: Ott Koik
Subject: Use coordinates with feature service SQL query

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-04-2020 10:54
From: Ott Koik
Subject: Use coordinates with feature service SQL query

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

Original Message:
Sent: 04-29-2020 16:28
From: Ott Koik
Subject: Use coordinates with feature service SQL query

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
------------------------------

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
------------------------------

Original Message:
Sent: 06-05-2020 16:42
From: Eric Blasenheim
Subject: Use coordinates with feature service SQL query

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

Original Message:
Sent: 06-04-2020 10:54
From: Ott Koik
Subject: Use coordinates with feature service SQL query

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

Original Message:
Sent: 04-29-2020 16:28
From: Ott Koik
Subject: Use coordinates with feature service SQL query

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
------------------------------