Skip to Main Content

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Maps performance suggestions?

AndyHFeb 3 2025

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?

Comments
Post Details
Added on Feb 3 2025
0 comments
41 views