M.App Enterprise Tutorials

[#1]

In this samples we are exploring the usage of the M.App Enterprise Query API to deal with database content in a Browser App.

Sample 1: Show attributes in Custom Panel of all the Airports in the current map extent

The App is comprised of 2 Panels:

• a Map panel displaying content from a VectorSet or any other content
• a Custom panel which will display a table containing information from a specific table based on the current map extent (only rows that are spatially contained in the current Map BBOX will be displayed)

Here is a video showing the App in action:

The App can be simply configured using the 2 panels and customizing them with the source code attached to this article (QueryAPI.zip).

The main idea is to build a SQL query that will perform a spatial relation from the Airports table and the current map extent. We are using a PostGIS database in this implementation, in the case of a SQLServer or Oracle database the following query should be adapted to the corresponding SQL dialect and provided Spatial functions:

select 
	id,
	site_no,
	lan_fa_ty,
	locid 
from 
	airports 
where 
	ST_Contains(ST_Transform(ST_MakeEnvelope(' + BBOX.x1 + ',' +  BBOX.y1 + ',' + BBOX.x2 + ',' +  BBOX.y2 + ',3857),2831),geometry_spa)'

where:

• airports is the table we will display content from
• BBOX.x1, BBOX.y1, BBOX.x2, BBOX.y2 are the coordinates that we get from the current extent of the Map panel (Javascript variables)
• geometry_spa is the native geometry in our database
• ST_Transform is function used to make a CS reprojection since our data in the DB are in EPSG:2831 and the Map is in EPSG:3857
• ST_Contains is the main function to do the spatial relation

We will issue this query using the M.App Enterprise Query API in the custom script of the Custom panel. In this panel we:

• subscribe to the event related to the WKE.ZoomMap message sent by the Map Panel
• execute the query using the performQuery method provided by the GSP API
• build a dynamic HTML table based on the columns returned by the query using the CreateTableFromJSON function

In the Map panel we:

• subscribe to the event mapMoved
• we build an object that cointains details about the current map status using the info method provided by the GSP API
• we send a message called WKE.ZoomMap containing the collected BBOX and CRS

Sample 2: Show attributes as message notification of the closest road to clicked point on the map

The app is comprised of just a Map panel.

Here is the app in action:

The App can be simply configured using the 1 panel and customizing it with the source code attached to this article (QueryAPI2.zip).

The approach is the same as in the sample 1. In this case we are executing the query directly within the Map panel using the coordinates of the point clicked on the map.

Here is the query definition:

select 
	osm_id,
	name,
	type,
	case oneway when 1 then '✓' else '✗' end as oneway,
	case bridge when 1 then '✓' else '✗' end as bridge,
	case tunnel when 1 then '✓' else '✗' end as tunnel 
from 
	roads 
where 
	St_DWithin(ST_GeometryFromText(\'POINT(' + X + ' ' + Y + ')\',2831),geometry_spa,2)

where:

• roads is the table we will display content from
• X, Y are the coordinates that we get on the MapClicked event (Javascript variables)
• geometry_spa is the native geometry in our database
• ST_GeometryFromText is function used to make a point geometry based on the coordinates
• ST_DWithin is the main function to do the spatial relation (returns true if the road and the point clicked on the map are within the specified distance)

We will issue this query using the M.App Enterprise Query API in the custom script of the Map panel. We:

• react to the fired event MapClicked 
• execute the query using the performQuery method provided by the GSP API
• show a message notification containing the result of the query

[#2]

Hello,

where can we find the documentation about the Query API?

Cheers, Nadia

[#3]

Hi Nadia,

in the code examples within M.App Editor for the browser based application there is a snippet:

var query = {
    layer: 'Airports',                  // Layer to query
    query: 'SELECT * FROM airports',    // Actual SQL syntax based query
    format: '.json',                    // Return format, '.json', 'geoJson' and '.csv' available
    srid: undefined                     // Optional parameter to convert the geometry result into a specific coordinate reference system
}

gsp.m_app.enterprise.performQuery(query).then(function(result) {
        
    // Result of the query provided in 'result'
});

You can find the restful endpoint in the swagger documentation:

https://[YOURSERVER]/swagger/ui/index#!/

Stefano