A possible Solution to the Problem I found working is a step-by-step solution which looks like this:
BEGIN;
CREATE TEMP TABLE route ON COMMIT DROP AS
SELECT seq, source, target, km, kmh, clazz, geom_way
FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex
WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box',
1362258, 1625523, FALSE, FALSE) AS route
LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id
ORDER BY seq;
CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id)
SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
UPDATE filling SET destination =
(SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <->
(SELECT geom FROM de_tt_stations WHERE id = filling.station_id)
LIMIT 1);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr,
(SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box
WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route
LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;
COMMIT;
Execution Time is fair with around 500ms and less. Is there any possibility to further optimize the Querys?
I know a bit about Oracle performance and pretty much nothing about custom data types, but I'll try to give you a plan to improve performance.
1) Verify that you cannot get an explain plan.
It's possible to get explain plans even if you don't have sophisicated database software. What happens if you execute set autotrace on explain
?
You could also try DBMS_XPLAN. First save off the plan by wrapping your query with a few extra key words:
explain plan for (SELECT... your query goes here);
Then execute this:
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
It's possible that neither of those will work and you truly cannot get an explain plan. I just wanted to verify that because with an explain plan it'll be much easier for the community to help you.
2) Consider requirements.
You said that 20 seconds isn't good enough. Have you or someone else defined exactly what is good enough? Is there any room for negotiation? Does your query need to be exactly one SELECT query? Could you populate a global temporary table in one step and select the results you wanted in the next? Could you create a stored procedure that returns a result set and call that?
3) Establish a lower bound for the time required to complete the query.
I suggest running a simple query that "cheats" to figure out what a well-optimized query would look like. For example, how long does this query that gets only the first vertices take?
SELECT
ROWNUM
,ROAD_ID
,VERTEX_INDEX
,SDE.ST_X(ST_POINT) AS X
,SDE.ST_Y(ST_POINT) AS Y
FROM
(
SELECT
ROWNUM
,a.ROAD_ID
,1 VERTEX_INDEX
,SDE.ST_PointN(a.SHAPE, 1) AS ST_POINT
FROM ENG.ROAD a
)
ORDER BY ROAD_ID, VERTEX_INDEX;
I suspect that will give you 4000 rows. If you multiply that query's response time by 17.5/4 that could give you a good lower bound for the total execution time.
If your lower bound for the total execution time is longer than what you established in step 2 then you either need to get creative with your data model by calculating results ahead of time and storing them in tables or you need to renegotiate the required response time.
4) Benchmark to figure out which functions are contributing the most to your execution time.
You were on the right track with Update #1 but you need to try to control for the amount of work being done. For example, is it possible to write a group of relatively simple queries that execute each function exactly 10000 times? How do the response times compare?
5) Go to work.
Depending on the requirements established in step 2 and what you found in step 4 try any trick that you can think of to reduce the query runtime. Are you able to pre-compute results and save off them? If the problem relates to the number of times the functions are executed then the undocumented materialize hint may be helpful. That forces Oracle to create a hidden temp table behind the scenes to store the results. I do not know if it is compatible with the special data types that you are using.
For example, maybe something like this performs better? Apologies if it does not compile but I have no way to test.
WITH ROAD_CTE (ROAD_ID, VERTEX_INDEX, SHAPE) AS
(
SELECT /*+ materalize */
a.ROAD_ID
, b.NUMBERS VERTEX_INDEX
, a.SHAPE
FROM ENG.ROAD a
CROSS JOIN ENG.NUMBERS b
WHERE b.NUMBERS <= SDE.ST_NUMPOINTS(a.SHAPE)
)
, CTE_WITH_ST_POINT (ROAD_ID, VERTEX_INDEX, ST_POINT) AS
(
SELECT /*+ materalize */
rcte.ROAD_ID
, rcte.VERTEX_INDEX
, SDE.ST_PointN(rcte.SHAPE, rcte.VERTEX_INDEX) ST_POINT
FROM ROAD_CTE rcte
)
SELECT
ROAD_ID
, VERTEX_INDEX
, SDE.ST_X(ST_POINT) AS X
, SDE.ST_Y(ST_POINT) AS Y
FROM CTE_WITH_ST_POINT
ORDER BY ROAD_ID, VERTEX_INDEX;
If you're still stuck after all of this I suspect that it'll at least give you additional information that you can edit into the question. Good luck!
Best Answer
Maybe this will help start you out but here is how I am thinking through this.
I can't think of any case, re spherical trig where two straight lines would be closer in the middle than at the edges, unless they cross so I think you can narrow your search down to the vertices of each road plus the nearest point to that vertex on the other road. I am assuming that your roads are modelled as line segments along great circles.
So I don't think that this is a complete solution but, what I would be looking at doing is:
That seems the best answer. You have to calculate points. I don;t know of any magic way to do this in PostGIS but it is something where spherical trig may come to the rescue. If the distances are sufficiently small, plane trig may be sufficient as an approximation, of course.