3

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.

  • 1
    As it stands I think your Question is too broad. For example what formats are your table and geometry currently in? What GIS software and version( s) do you have access to ? – PolyGeo Apr 10 '14 at 20:23
  • @PolyGeo edited with (I hope) enough info... – DPSSpatial Apr 10 '14 at 20:28
  • One to many join? – the_skua Apr 10 '14 at 20:58
  • @the_skua performing that type of join in ArcMap, for example, doesn't work in either direction. – DPSSpatial Apr 10 '14 at 21:12
  • Is the school geometry a point or polygon? Are you trying to locate incidents to a point or just "within the school"? – BradHards Apr 10 '14 at 21:49
2

I assume, from your question and from some of the comments on the original question, that you are trying to perform ideally a one to many join and are trying to do this in ArcGIS Desktop software. Unfortunately, the basic join in ArcMap available via right click on the layer doesn't work with one to many joins well, but if that's what you're after, there are two ways you can do this I know of:

IMPORTANT POINT FOR EITHER METHOD: You can only perform joins in ArcGIS Desktop software between two fields of the same type, so if you've go an ID field as a Text Field in one table and as a Short Integer field in another, you're going to have to create a new field of the other field type in one of the two tables and then calculate that new field from the existing ID field before you can perform the join. If it's just a one to one join, this is all you should have to do and then just join the incident data to the school and you're done; if there are multiple incident records per school though, read on.

First, and personally the most obvious, you can use the Make Query Table geoprocessing tool which is designed to perform one to many joins. You can get some specifics at http://support.esri.com/en/knowledgebase/techarticles/detail/37544 for a step by step ESRI KB article or http://resources.arcgis.com/en/help/main/10.2/index.html#//00170000006r000000 for the tool help. This will only output a temp layer, but you can just export the results to a permanent dataset using Feature Class to Feature Class, Table to Table, or Copy GP tools, depending on which direction your joining.

The other method to do a one to many join would be to add an x field and y field to your schools Feature Class and use the calculate geometry option by right clicking on the field in the attribute table in ArcMap so you populate those fields with the lat and long. Then you could do a one to one join, joining the schools with geometry to the incidents based on the ID field. Then you would use this table to create a new Feature Class from the resulting XY data.

Hope that helps, if not, let me know with a comment and I'll see what I can do to clarify.

  • I can't seem to get the tool to accept the point file as either SDE or SHP... reading the doc you linked to and the tool help that it only takes TABLES rather than feature classes / geometry...? – DPSSpatial Apr 10 '14 at 22:04
  • Ok, one other thing you need to try: Try importing your excel spreadsheet into the SDE (or import it into a file-geodatabase and export the SDE feature class to the same file-geodatabase). Sometimes it makes a difference if inputs are from the same workspace. I've used it on feature classes plenty before though, not just tables so that's not it. I've done exactly what you're trying to do, had a feature class and trying to join multiple spreadsheet rows to them, but ESRI has issues with spreadsheets sometimes, try importing it to FGDB and let me know. – John Apr 11 '14 at 5:00
  • I did import the excel file into a FGDB, but the issue seems to be with adding the school points SDE feature class (also tried as shapefile) that it didn't like... will try again to make sure its the workspaces that aren't causing me troubles. – DPSSpatial Apr 11 '14 at 15:11
  • weird issue with this tool: I got it to work by putting both the schools shapefile and the incidents table into a FGDB... when I run the tool - in most combination of parameters I can determine might work - the result point query layer balloons to over 4,000,000 records! The input school points have 196 features, and there are 20,000 incidents in the table... – DPSSpatial Apr 11 '14 at 15:59
  • 1
    Not sure then, it really sounds like it's having an issue interpreting the expression, but without it being in-front of me... I really woudldn't where else to start trouble-shooting. If you've got esri-support you can contact them, otherwise there is the other method I gave that I don't see why it wouldn't work. If the other, basic joint based method, doesn't work, then it's probably an issue with your fields not joining correctly. I'd re-check your field types and field values (ex: leading spaces/zeroes in one & not the other) at that point. Sorry can't be of more help. – John Apr 11 '14 at 20:03
2

I suspect you want to keep the incidents file as an excel file (to continue monitoring)? Probably the most straightforward way to do this would be to use Excel lookup functions to create geometry in the Excel file, which can then be used for mapping directly.

  1. Add X and Y colums to the geometry point file, and save-as a CSV file;
  2. Open the CSV in Excel and copy it as a new tab ('locations') into your Excel file;
  3. Add X and Y columns to the original Excel Incidents tab, and use a lookup function to get the X and Y values from the locations tab;
  4. Map incidents directly in Arc (or QGIS using the XY plugin) by reading the Excel file.

In detail

Add X and Y columns to your point SDE layer (I don't use ArcGIS but there are instructions here). Export the attribute table to a CSV file and open in Excel:

Schools point table:

id_school   X     Y
1           32.1  -2.3
2           33.0  -2.4
3           32.0  -2.0

Add it as a tab to your 'incident' Excel file; the 'incidents' might look like:

id_school   Incident     Date
1           A            2014-04-01
1           B            2014-04-02
2           A            2014-04-01
3           B            2014-04-11

Add an X and Y column to the incident tab, and use the vlookup function to add the values to those columns, using the format vlookup(id_school, points_tab_range, column number):

id_school   Incident     Date        X
1           A            2014-04-01  =vlookup(A2, points!A1:C5, 2)

The X column would then be populated with the value 32.1. Do the same for the Y column but with column 3 indicated. If you update schools or locations, add them to the lookup range.

You can then add the Excel file as a layer in ArcGIS; if using QGIS, the 'XY Tools' plugin will read .XLS (not .XLSX unfortunately) in the same way.

  • Much appreciate your response. It is not necessary to keep the excel file in its current format. I will try this just the same! Thanks again! – DPSSpatial Apr 11 '14 at 15:13
0

Use join, I guess since SDE is mentioned you have access to ArcMAP, add the school points and the excel spreadsheet as a table to the Data Frame, then right click point layer select join and use the field that has the same data on both of the tables that should do the trick

  • see comment above... straight up joins in either direction do not work in ArcMap to solve this problem. – DPSSpatial Apr 10 '14 at 21:49
  • I assume you don't want to just do a relate? – ianbroad Apr 11 '14 at 15:33
  • @ian a new shapefile / feature class is what i'm after. – DPSSpatial Apr 11 '14 at 15:39
  • @mapBaker So you don't care if the points are duplicated on top of each other? You just want a point for each incident? – ianbroad Apr 11 '14 at 15:40
  • @ian not at all... the goal is to do time-series analysis / animations. – DPSSpatial Apr 11 '14 at 15:57
0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.