northrivergeo / tn_ng911_data_model Goto Github PK

So this just appeared yesterday - new layers for the database. I'm trying to get the paperwork and see what needs to be built. It should be three tables and some triggers. It seems to be built out of the NENA Database - SOOOo - I may be ahead of the game.

Back to this problem after I decided it was undoable - I've had another idea that migh be slightly less painful than the first one.

Every address needs a zipcode added. I've been leaving that up to the 911 BUT - in general they are using the ZCTA layer from the census. So my thought is to set up a trigger for addresses and to go ahead and set up a schema for census and set up a zipcode layer for all of TN. It shouldn't be large - maybe go ahead and convert it to SQL using shp2pgsql or the QGIS tools.

Cloud you use QGIS server as a "web map" for the 911 TN 911 service.

I checked the TN 911 Standard. The city can't be assigned from the Census. That has to be assigned based off the local county MSAG layer. I can write a quick trigger for that, but that's likely just has to be a placeholder for now.

Need to finish up the overlapping road ranges script.

Implement pgrouting for drive times from fire stations. It may be easier to do this in QGIS vs a postgis extension.

In some cases during maintenance I need to disable the triggers. So there will be one script to disable and you can re-run script 04 to re-enable.

I'm adding this in as a placeholder until I give it some more thought.

Need to set up a "pre-addressing" script in either QGIS or Postgis for assigning addresses every X feet on a new (or old) stretch of road.

I need to start documenting this in some way so people know "what gets run when". I'll probably do a doc of some sort or something on the WIKI up here.

Create a script to build a MSAG

Structype 13 is Child Day care and Structype 14 is Adult Daycare. That will be in the 04 script for building tables.

I need to do a trigger for the city/county boundaries in TN - similar to the ZCTA. I can get county boundaries from census.....not sure on city boundaries. I'll do some digging.

Table names, schema names, and column names do not match. Standardization needs to be implemented between scripts and the data.
For example: OGR2OGR puts geometries in as wkb_geometry but the script references the column geom.
OGR2OGR also specifies address_points and esn but the QA/QC Script references addresspoints and esn_boundary.

Automatically move your cards to the right place based on the status and activity of your issues and pull requests.

So this will be more of a "cleanup" thing since you can split the streets in the software - but what I'm seeing is not many are doing it. This will also cause a slight issue with road ranges once we run the split.

Road/address type needs to have the most used in TN at the top by usage. Shouldn't be hard but it would make scrolling through menus less of a pain.

So there is a script - create_qgis_form_table.sql

What this does is builds a table from which I can create QGIS forms. I think the table/values list is complete. I generally load the tables into QGIS and then group them under "forms" to hide them from the user.

The best way to se this up due to the number of forms is probably pyqgis. Have a python script to load the tables and then assign the right widget to the right field. that way if a user loses the widgets or restarts a new project die to a crash that can be recovered quickly.

I have the GPSdate set up as a date field on the address points and I think it needs to be a timestamp.

R_SEGID is for Routing
A_SEGID is for Access

Right now in the data model there is no "connector" between the address point and the centerline. So that must be manually entered and not assigned through a menu. I don't know if it would be possible to set up a way to select the road and then "assign" it as access, routing, or both in QGIS.

Made one attempt at fishbones. Need to do another but make it one script and possible create a fishbone when the address is added - OR fishbone old addresses and pre-address new streets.

So incase I need to edit the reference tables I need to drop constraints.

We need to tie the address to the centerline segment. That way it opens up to checking whether a road/address share commonality with ESN, City, Zip, and spelling. Shouldn't be too hard to do.

Segid is assigned automatically when a new centerline is added. If you split the centerline you will create a new Segid on one part of the line.

Assuming there are existing addresses on the new SEGID those new addresses will be out of sync as they have the old centerline.

So I need a way to automatically go "update these addresses with the new segid".

Can this be added in as part of the QA/QC Schema?

https://www.northrivergeographic.com/archives/fishbones-for-the-tn-ng911-address-server

Got these as a shapefile: Police, EMS, Fire - I need to add these to one of the build scripts. That brings the total number of needed layers to 6.

Pick a unified naming convention for table names, and then go through all SQL scripts and apply the unified naming convention.

@rjhale1971, should it be:

1. addresspoints or address_points
2. esn or esn_boundary?

I just want to confirm and then I can update all table names to get on the same page.

Something isn't working right here:

NEW.label := initcap(concat_ws('' '', new.predir, new.pretype, new.name, new.type, new.sufdir, new.postmod));

So it's either QGIS (running master) OR it's something with the SQL.

Get a Cost for TFCost and FT Cost for centerlines. I'll need to call Henry County to set that up for the test dataset for pgrouting. This is stupid low priority but it could be fun.

Don't have that working quite yet - I'm removing things that the initial client needed for the CAD system.

In the centerline dataset there are 3 fields that Autoincrement (of sorts).

OIRID is referenced by the state and can't change for the 3 layers (address, esn, centerline). That is built by taking the ID field and adding "Henry_" to it. So you have 'Henry_1', 'Henry_2' etc.

Segid doesn't increment or do anything It needs to. We can do this one of two ways.
* We set it up to increment on it's own and it doesn't rely on the ID.
* it calculates off the ID with an update trigger. I sorta like this idea because the ID is the primary key for that layer.

If we calculate it off the ID with an update trigger more or less it's something like:

CREATE OR REPLACE FUNCTION centerlines_segid()
RETURNS TRIGGER AS '
BEGIN
NEW.segid := new.id;
RETURN NEW;
END;
' language 'plpgsql';

CREATE TRIGGER update_centerlines_segid before insert
on tn911.centerlines FOR EACH ROW EXECUTE PROCEDURE
centerlines_segid();

I added this code to the create_vector_functions.sql script. It's not been tested.

Streets have left and Right sides. In the data you'll see this as "L_from" and "L_To".

Ranges can be 0-10, 11-20, 21-40 etc. There can't be overlap. In some cases where the ranges are manually generated thee is overlap: 0-20, 15-30, 25-50. The overlap can create problems for the dispatch software and geocoding.

I have code that checks using postgresql through an inner join - I'm not sure how to implement this as a constant check in the database. In other words - I have something that runs on new data and checks ranges. I've been using this as a "one off".

select ilv1.label, ilv1.l_f_add::int, ilv1.l_t_add::int, ilv1.id, ilv2.label, ilv2.id, ilv2.l_f_add::int, ilv2.l_t_add::int
from (select id,
label,
l_f_add::int,
l_t_add::int
from tn911.centerlines) ilv1
inner join (select id,
label,
l_f_add::int,
l_t_add::int
from tn911.centerlines) ilv2
on ilv1.label = ilv2.label
and ilv1.id < ilv2.id
where ilv1.l_f_add::int between ilv2.l_f_add::int and ilv2.l_t_add::int
or ilv1.l_f_add::int between ilv2.l_f_add::int and ilv2.l_t_add::int;