Eagle Pipeline GIS Blog

How to query events on a particular route in PODS

Tweet  

  

 delicious  

  

A PODS relational database can be a tricky thing to query especially if you are not familiar with the complicated joins and flags that normally accompany such a complex and rich data model.  In this case, I will show how to construct a query for a PODS 3.2.1 Relational model with history.  I will not cover “inline vs offline” history here since offline is not technically supported by PODS at this time.

First start by joining the line, route and series tables.  In this case we will leave off the route table since we already know what route id we are going to look at:

select * from line l, route r, series s1, series s2 where
r.route_id = ???
and r.line_id = l.line_id
and s1.route_id = r.route_id
and s2.route_id = r.route_id
and s1.current_indicator_lf = 'Y'
and s2.current_indicator_lf = 'Y' 

Notice that I have included the series table twice, but joined them on route id.  This is a must since events can span over a series, so we could have events that start on one series and end on another.  We also need current indicator flags to ensure that we only get current series in our output.  When I execute the SQL I replace the ??? with the route id that I am looking for.  When reviewing the model of the series table you may be tempted to simply use the line_id column in the series table, lets take a look at that.

There is a hierarchical structure that starts with line and goes down to series through the route table, so there is no constraint on the line_id column in the series table. Which means it may be correct, or it may not be.  Similar columns also exist in the station_point table, where both a line_id and route_id exist, but are not properly constrained!  You should not use these while querying!  Some say that these will significantly speed up the query by removing joins, this is probably true, but in a modern RDBMS with good indexing the difference will be little, and using the full join ensures that your data is accurate.

There are several inconsistencies in the PODS model like this one, we will look at those in another blog! 

Next, we will join in the station point table.  The station point table is where PODS keeps all of the linear referencing information, and is how events and coordinates are located on the linear referenced centerline.  As before, we will need two station_point joins in order to account for features that span series. This time joining each station point to its respective series table.

select * from line l, route r, series s1, series s2, station_point sp1, station_point sp2 where
r.route_id = ???
and r.line_id = l.line_id
and s1.route_id = r.route_id
and s2.route_id = r.route_id
and s1.current_indicator_lf = 'Y'
and s2.current_indicator_lf = 'Y'
and sp1.series_id = s1.series_id
and sp2.series_id = s1.series_id

Then, we join in the event range and feature table tables.  This will give us access to the event pointers, and the ability to know what table they are stored in:

select * from line l, route r, series s1, series s2, station_point sp1, station_point sp2, event_range er, feature_table ft where
r.route_id = ???
and r.line_id = l.line_id
and s1.route_id = r.route_id
and s2.route_id = r.route_id
and s1.current_indicator_lf = 'Y'
and s2.current_indicator_lf = 'Y'
and sp1.series_id = s1.series_id
and sp2.series_id  = s2.series_id
and er.station_id_begin = sp1.station_id
and er.station_id_end = sp2.station_id
and er.current_indicator_lf = 'Y'
and ft.feature_id = er.feature_id

Now we will add some detail around the select clause to get some useful columns:

select l.description as LINE_DESCRIPTION,
l.line_id as LINE_ID,
r.description as ROUTE_DESCRIPTION,
r.route_id as ROUTE_ID,
s1.series as FROM_SERIES,
sp1.station as FROM_STATION,
s2.series as TO_SERIES,
sp2.station as TO_STATION,
sp1.measure as FROM_MEASURE,
sp2.measure as TO_MEASURE,
ft.table_name as TABLE_NAME,
er.event_id as EVENT_ID

If you are writing queries like this a lot, I strongly recommend that you take some time to review the model in detail. There are many secrets that PODS has that can catch you off guard! I have included a PDF at the end of this blog with the completed query for your convenience. 

Bonus Query (also included below) - Get a feature count per route by type of feature!  Please note that this query does not contain a restriction on the route_id, so it will run against ALL routes!  This can take a LONG time, so if you have a large database, restrict it to a set of routes. 

Completed Query Free Download

Bonus Query