APEX 24.1
My underlying data is in OSGB (SRID 27700).
I have a map with multiple layers. Layers are either points or lines. My lines are defined in the query and I'm interested in the length of the line, which is defined as the distance between two objects e.g.
SELECT sdo_geometry(2002
,27700
,NULL
,sdo_elem_info_array(1
,2
,1)
,sdo_ordinate_array(mepo.spatial_location.sdo_point.x
,mepo.spatial_location.sdo_point.y
,node.spatial_location.sdo_point.x
,node.spatial_location.sdo_point.y)) AS line
,round(sdo_geom.sdo_distance(mepo.spatial_location
,node.spatial_location
,1
,'UNIT=METRE')) node_distance
In this case, there are two tables ‘mepo’ and ‘node’ - mepo has a foreign key referring to node. All spatial_locations are indexed.
I'm limiting the query by using a bounding box defined by the map region, which is generated as geo json. I look for any interaction between the geo json and the mepo object:
AND sdo_anyinteract(mepo.spatial_location
,sdo_util.from_geojson(:p80_bbox)) = 'TRUE')
Returning this map layer can take 90 seconds or more (around 30,000 mepo objects, 3,000 node objects).
Quick queries:
- Does the mismatch of SRIDs impact? e.g. my underlying data (OSGB) vs the map region (Lat Long) vs geojson (Lat Long)
- If so, should I make the spatial information consistent within the query, or should it occur within the objects themselves e.g. have 'spatial locations' for each of the SRIDs my application users might require?
- As the bounding box is a rectangle, would it be beneficial to define that geometry as a rectangle by parsing the geo_json within the query?
- Should filtering occur in the query or the returned data, e.g. if I only want to show ‘node_distances’ > 200m, I can either wrap my query with a ‘where’ clause or let APEX do it as as ‘server-side expression’ - or are these effectively the same thing?
- Are there any ‘practical’ limits regarding the number of objects displayed in the map region? e.g. is 30,0000 lines considered a big, small or ‘normal’ number to be processed on a map region?
- Does the number of objects displayed affect the user's browser in terms of memory, etc.?
Any thoughts?