We want to store the maps (GIS) data into the database. The database provided by outsystem does not provide the Geometry (shape) datatype. Having data stored as Geometry datatype provides access lot of server GIS functions (like STIntersection, STUnion ). SQL server provides support for Geometry datatype.
The underlying database for Outsystem being SQL server, would like to know if outsystem could provide such access. If not, what is the alternatives, one could have?
Use case here, We are rendering shape layers on the map. When we fetch a layer from DB, we would like to fetch all the intersecting shape layer also.
Hi, could it be in binary format?
Regards,
Are you trying to save the .shp files raw data to Outsystems entities, or want to save the values like double long int coordinate values?
https://learn.microsoft.com/en-us/sql/relational-databases/spatial/spatial-data-types-overview?view=sql-server-ver16
https://success.outsystems.com/documentation/11/reference/outsystems_language/extensibility_and_integration/mapping_external_db_data_types_to_outsystems_data_types/
no direct mapping available.
However check this
https://galter.outsystemscloud.com/sqlapp/SpatialData.aspx?(Not.Licensed.For.Production)=
Spacial data tab
USING Spatial Data (SQL Server)
Spatial DataSpatial data represents information about the physical location and shape of geometric objects. These objects can be point locations or more complex objects such as countries, roads, or lakes.SQL Server supports two spatial data types: the geometry data type and the geography data type.The geometry type represents data in a Euclidean (flat) coordinate system.The geography type represents data in a round-earth coordinate system.Both data types are implemented as .NET common language runtime (CLR) data types in SQL Server.
Hi,
If you are looking into Point Geometry you can represent that point also as decimal values for Latitude and Longitude. In in advanced sql you can cast Lat and Lng to a Point back. See this example which returns the distance of stored city entries from a Lat and Lng coordinate parameter.
SELECT TOP(@MaxResults) {Geo}.[CityName] AS Name,{Geo}.[CityCode] AS Code,{Geo}.[CommunityName] AS Community,{Geo}.[CityZipCode] AS ZipCode,{Geo}.[CityLat] AS Latitude,{Geo}.[CityLon] AS Longitude,ROUND(geography::Point(@Latitude,@Longitude, 4326).STDistance(geography::Point({Geo}.[CityLat],{Geo}.[CityLon], 4326)) / 1000,1) AS DistanceFROM {Geo}ORDER BY [Distance] ASC
The ability to store a shape as a GEOMETRY data type in a TABLE column, allows the definition of a SPATIAL index (on the GEOMETRY shape).
THEN, given A boundary, use cases like finding its nearest neighbors or intersecting boundaries, etc. can be efficiently & performantly solved using spatial SQL functions, which MAKE USE OF THE SPATIAL INDEX.
You don't store as a GEOMETRY data type --> you don't get the INDEX --> you do a TABLE scan versus an INDEX scan --> it doesn't work.
Whether points or lines or polygons, storing them as Text, and CASTing will solve 1 in a million GeoSpatial problems at most, and as a workaround.
To support GIS apps, the OutSystems platform needs to add first class support for the GEOMETRY data type when defining an Entity, with the caveat that it will only work if the underlying database supports the type - e.g. MS SQL Server, e.g. PostgreSQL.
Attn: @Stefan Weber @Dan Fauxpoint
Excellent comment @Manoj Seshan!
Because O11 and ODC (with Aurora PostgreSQL) do not support Geometry and Geography data types, I think the only solution is to extend the platform using external code that can directly access the database with tables that have columns of the geometry or geography data types.
--Tiago Bernardo