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.

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110

4 Answers4

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.

John
  • 3,853
  • 12
  • 17
  • 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_BoycottingGISSE 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 05: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_BoycottingGISSE 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_BoycottingGISSE Apr 11 '14 at 15:59
  • That is odd. The only thing I can think of would be if you're not using the Expression field correctly. In that field is where you should be specifying the join field. For example, from one of my uses of that tool I put input_parcel_points_date.PIDLong = nacocad_data_date.ParcelID because I was trying to join the two based on the PIDLong and ParcelID fields matching. Also, FYI, of the two inputs (one table one FC), the feature class has to be listed first in the inputs table so the output is a layer not a table. Double check the expression is what I'd do with that. – John Apr 11 '14 at 17:23
  • Still no luck. Order of inputs is how you have specified, expression is as you specified but is not being taken by the tool, result still over 4,000,000... – DPSSpatial_BoycottingGISSE Apr 11 '14 at 19:25
  • 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
  • thanks so much for your help, it has been valuable just to dig into these other methods! – DPSSpatial_BoycottingGISSE Apr 11 '14 at 20:17
  • John and @mapbaker: I've been trying to understand this problem and working through it the last couple of hours. One thing I could not figure out is why everyone kept saying a join wouldn't work. If correct, I figured out that the join does work, but you cannot create geometry with a join, only assign attributes. Since schools have no geometry, the result of the join is still just a table. As for MQT not working, John states Expression being the problem, but that is to subset records right? Would it more be if too many key fields were marked that would give the exponential returns? – Chris W Apr 18 '14 at 23:13
  • @ChrisW, if you read the documentation on that tool and the ESRI KB article I provide links to in the answer, it shows the expression, in the case of this particular tool, actually being what you use to define the joins. Yes, it I guess would also define a subset by definition of what it is, but that same expression is what's matching up the join. The key fields are actually used to define ObjectID creation, not perform the join. – John Apr 21 '14 at 04:42
  • @ChrisW yes the Schools do have geometry... the result would be the incidents table with another column for 'geometry' which would be joined from the Schools feature class. An ArcGIS join cannot do this, only a relational database with geometry columns such as SQL Server or PostGIS. – DPSSpatial_BoycottingGISSE Apr 21 '14 at 16:04
  • John: I didn't save my results from before the comment, but I thought I had it working correctly without using an expression. I can't duplicate that now so I must be mistaken - no expression gives all possible combinations, not just matching combinations. The tool help confused me since expression is 'optional' and described as "to select a subset of records." @mapBaker I mistyped there - I meant to say incidents had no geometry. And to further clarify it will do the join in that direction, but not bring over (rather than create as I said above) the geometry field for whatever reason. – Chris W Apr 21 '14 at 17:29
  • @ChrisW yes, what is that reason??? – DPSSpatial_BoycottingGISSE Apr 21 '14 at 17:36
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.

Community
  • 1
Simbamangu
  • 14,773
  • 6
  • 59
  • 93
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

Antonio Locandro
  • 580
  • 3
  • 13
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!

DPSSpatial_BoycottingGISSE
  • 18,790
  • 4
  • 66
  • 110