We are looking to map out 'incidents' at schools, but the incident table, which contains a few thousand records, only has a school number (plus the incident data). The table format is an Excel Spreadsheet with around 20,000 records.
We have schools (around 250 records/points) that share the same ID and of course the geometry for each school. The schools are a point SDE feature class, but can be converted to SHP or other geometry type if needed.
How can we assign the geometry from the schools to each record in the 'incidents' table?
I'd like to have something like a shapefile or other type of table. Keeping the excel file in its current format is not necessary, ie. we're converting the excel file to a feature class / shapefile / etc.
Any software option is on the table.
Answer
The solution to this problem involves the use of relational database with geometry columns such as SQL Server or PostGIS.
I first loaded the School points table (containing geometry and SCHNUM columns), and the incidents table (including the SCHNUM and OBJECTID columns, as well as other columns of incident data) into the relational database.
I then simply joined the geometry from the school points to the incidents on the SCHNUM field using a LEFT JOIN.
I then created a view of that join.
From there, the 'geo-enabled incidents' view can be consumed in either ArcMap (through SQL Server) or QGIS (through PostGIS).
The data can also be extracted from that view to a static shapefile or SDE table using ArcMap 'export data' (by connecting to the SQL Server database), or an ETL tool such as FME Workbench, for example.
The points of course overlap, but since we're doing time analysis on the incidents, this is just fine. This also gives the ability to do density analysis on certain types of incidents as well.
The key to all of this is that using a relational database with geometry enabled gives the option to treat geometry like any other data in the database...Spatial IT!!!
Thank you all for your help!
No comments:
Post a Comment