Difference between revisions of "Import Geospatial Data from Excel"

From IMSMA Wiki
Jump to: navigation, search
 
(14 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
<p>Various windows in IMSMA allow you to import geospatial data from external source files. These external source files can be either Excel spreadsheets (with a .xls or .xlsx file extension), or shapefiles (with a .shp file extension).</p>
+
To import geospatial data from Excel file:
−
<p>To import geospatial data:</p>
 
 
<ol>
 
<ol>
−
<li>Click the [[Image:EditReportIcon.png | Import Points]] button.</li>
+
<li>Click the [[Image:EditReportIcon.png |Import Points]] button.
−
:The Open window displays.
+
[[Image:PointandPolyListWindow.png|500px|center]]</li>
 +
<li>The Open window displays.</li>
 
<li>From the Open window:</li>
 
<li>From the Open window:</li>
 
<ol type="a">
 
<ol type="a">
 
<li>Navigate to the directory where the file that you would like to import is located.</li>
 
<li>Navigate to the directory where the file that you would like to import is located.</li>
−
<li>Select the file name.</li>
+
<li>Select the file name (xls or xlsx).</li>
 
<li>Click the '''Open''' button.</li>
 
<li>Click the '''Open''' button.</li>
 
:The geospatial data from the spreadsheet is appended to any existing data in the Points or Polygons table.  
 
:The geospatial data from the spreadsheet is appended to any existing data in the Points or Polygons table.  
Line 13: Line 13:
 
</ol>
 
</ol>
  
−
===Excel Import Business Rules===__NOEDITSECTION__
+
==Excel Import Business Rules==__NOEDITSECTION__
−
 
+
{{Note |To import geospatial data from an Excel spreadsheet, the file must:
−
<p>To import geospatial data from an Excel spreadsheet, the file must be created according to the following business rules:</p>
+
* contain the columns in the table below
−
<ol>
+
* the columns must have the specified name
−
<li>The columns in the spreadsheet must be in a certain order from left to right.</li>
+
* the columns must be in the specified order
−
</ol>
+
* the first row of the file should contain the column names
 +
* the used decimal symbol in the Excel file must be the same as the Regional settings of the importing computer.}}
  
 
{| class="wikitable" width="600"
 
{| class="wikitable" width="600"
Line 31: Line 32:
 
|  PolyID || The ID for the polygon or polyline.
 
|  PolyID || The ID for the polygon or polyline.
 
|-
 
|-
−
|  PointNo || The point number for the point. The point identified as point 1 will be imported as a starting point; all other points will be imported as a turning point.
+
|  PointNo || The point number for the point.  
 
|-
 
|-
 
|  PointID || The ID for the point.  
 
|  PointID || The ID for the point.  
Line 50: Line 51:
 
|}
 
|}
  
−
<ol start="2">
+
Examples of the Excel files with '''dot''' as decimal symbol
−
<li>The first row of the spreadsheet should not contain data because it is ignored by the import process. You should use the first row of the spreadsheet for column headings.</li>
+
* WGS 84
−
</ol>
+
* UTM
−
 
+
* UTM with distance and bearing
 +
* MGRS with distance and bearing
 +
can be downloaded from '''[[Media:ExcelImportTemplate.zip | here]]'''.  
  
−
[[Image:SampleSpreadsheetForGeospatialData.jpg||''Sample Spreadsheet with Geospatial Data'']]
+
[[Image:SampleSpreadsheetForGeospatialData.png|800px|center]]
−
<div align="left">
+
<div align="center">
 
''Sample Spreadsheet with Geospatial Data''
 
''Sample Spreadsheet with Geospatial Data''
 
</div>
 
</div>
  
 +
<ol>
 +
<li>The selected coordinate reference system in the ''Point and Polygon/Polyline List'' window is the coordinate system need to be the same as the coordinate data in the Excel input file.  Please note that the ProjName column in the Excel input file is purely for manual reference and not used by the import from Excel function.</li>
 +
<li>When a '''Poly ID''' is provided, the coordinate data will be a polygon/polyline point.  Otherwise, the coordinate data is considered a '''single point'''.</li>
 +
<li>If you would like to import geospatial data for multiple polygons/polylines, use different PolyIDs, keep the points for each shape together in the spreadsheet and make the Point ID unique in the file. The example shown below illustrates a spreadsheet which defines two polygons.
  
−
<ol start="3">
+
[[Image:SampleSpreadsheetWithDataForTwoPolygons.png|800px|center]]
−
<li>If you would like to import geospatial data for multiple shapes, keep the points for each shape together in the spreadsheet and begin each new shape with point number 1. The example shown below illustrates a spreadsheet which defines two polylines.</li>
+
<div align="center">
−
</ol>
+
'' Sample Excel file with coordinates for two polygons ''
−
 
+
</div></li>
−
[[Image:SampleSpreadsheetWithDataForTwoPolylines.png||600px|''Sample Spreadsheet with Data For Two Polylines]]
 
−
<div align="left">
 
−
''Sample Spreadsheet With Data For Two Polylines''
 
−
</div>
 
−
 
 
−
<ol start="4">
 
−
<li>The Point ID cannot be NULL, and must be unique across all points in the Excel spreadsheet and existing points in the Points table and Polygon/Polyline points table at the geospatial widget of the target object.</li>
 
−
<li>The Point Type must be valid.</li>
 
−
<li>When a '''Poly ID''' is provided, the coordinate data is a polygon/polyline point.  Otherwise, the coordinate data is considered a '''single point'''.</li>
 
 
<li>The PointNo cannot be NULL for any polygon/polyline points.  If the PointNo is NULL and the coordinate is a single point, the PointNo will be set to 1.</li>
 
<li>The PointNo cannot be NULL for any polygon/polyline points.  If the PointNo is NULL and the coordinate is a single point, the PointNo will be set to 1.</li>
 +
<li>The Point ID cannot be NULL, and must be '''unique''' across all points in the Excel spreadsheet '''and''' existing points in the Points table and Polygon/Polyline points table at the geospatial widget of the target object.</li>
 +
<li>The Point Type must be valid. But if Point Type is not filled in, point 1 will be imported as a starting point; all other points will be imported as turning points.</li>
 +
<li>The Xcoord and Ycoord values must conform to the coordinate reference format selected at the window.  Available coordinate formats are: ''dd'', ''dd:mm'', ''dd:mm:ss'', ''X/Y'', and ''MGRS''.</li>
 
<li>If the Bearing, Distance, and FrPointID are not ''NULL'', the Xcoord and Ycoord values will be disregarded/voided.</li>
 
<li>If the Bearing, Distance, and FrPointID are not ''NULL'', the Xcoord and Ycoord values will be disregarded/voided.</li>
 
<li>The Bearing value must be in the range of -360 to +360.</li>
 
<li>The Bearing value must be in the range of -360 to +360.</li>
−
<li>The Xcoord and Ycoord values must conform to the coordinate reference format selected at the geospatial widget. Available coordinate formats are: ''dd'', ''dd:mm'', ''dd:mm:ss'', ''X/Y'', and ''MGRS''.</li>
+
<li>If Distance/Bearing is used than the first point of the polygon(s) must have coordinates i.e. the first point cannot be relative to e.g. the Benchmark.</li>
−
<li>The selected coordinate reference system at the geospatial widget is the coordinate system for the coordinate data in the Excel input file. Please note that the ProjName column in the Excel input file is purely for manual reference and not used in the Point Editor Excel Import function.</li>
 
 
</ol>
 
</ol>
  

Latest revision as of 14:47, 22 November 2017

To import geospatial data from Excel file:

  1. Click the Import Points button.
    PointandPolyListWindow.png
  2. The Open window displays.
  3. From the Open window:
    1. Navigate to the directory where the file that you would like to import is located.
    2. Select the file name (xls or xlsx).
    3. Click the Open button.
      4. The geospatial data from the spreadsheet is appended to any existing data in the Points or Polygons table.

Excel Import Business Rules

Note.jpg To import geospatial data from an Excel spreadsheet, the file must:
  • contain the columns in the table below
  • the columns must have the specified name
  • the columns must be in the specified order
  • the first row of the file should contain the column names
  • the used decimal symbol in the Excel file must be the same as the Regional settings of the importing computer.
Geospatial Data Spreadsheet
Column Description
ProjName The coordinate system for the point.
PolyID The ID for the polygon or polyline.
PointNo The point number for the point.
PointID The ID for the point.
PointType The type of point (Benchmark, Start Point, Turning Point, etc.).
Xcoord The longitude of the point in the correct format.
Ycoord The latitude of the point in the correct format.
Distance The distance of the point from the point referenced by FrPointID.
Bearing The bearing of the point from the point referenced by FrPointID.
FrPointID The from point.
PointDesc The point description.

Examples of the Excel files with dot as decimal symbol

  • WGS 84
  • UTM
  • UTM with distance and bearing
  • MGRS with distance and bearing

can be downloaded from here.

SampleSpreadsheetForGeospatialData.png

Sample Spreadsheet with Geospatial Data

  1. The selected coordinate reference system in the Point and Polygon/Polyline List window is the coordinate system need to be the same as the coordinate data in the Excel input file. Please note that the ProjName column in the Excel input file is purely for manual reference and not used by the import from Excel function.
  2. When a Poly ID is provided, the coordinate data will be a polygon/polyline point. Otherwise, the coordinate data is considered a single point.
  3. If you would like to import geospatial data for multiple polygons/polylines, use different PolyIDs, keep the points for each shape together in the spreadsheet and make the Point ID unique in the file. The example shown below illustrates a spreadsheet which defines two polygons.
    SampleSpreadsheetWithDataForTwoPolygons.png

    Sample Excel file with coordinates for two polygons

  4. The PointNo cannot be NULL for any polygon/polyline points. If the PointNo is NULL and the coordinate is a single point, the PointNo will be set to 1.
  5. The Point ID cannot be NULL, and must be unique across all points in the Excel spreadsheet and existing points in the Points table and Polygon/Polyline points table at the geospatial widget of the target object.
  6. The Point Type must be valid. But if Point Type is not filled in, point 1 will be imported as a starting point; all other points will be imported as turning points.
  7. The Xcoord and Ycoord values must conform to the coordinate reference format selected at the window. Available coordinate formats are: dd, dd:mm, dd:mm:ss, X/Y, and MGRS.
  8. If the Bearing, Distance, and FrPointID are not NULL, the Xcoord and Ycoord values will be disregarded/voided.
  9. The Bearing value must be in the range of -360 to +360.
  10. If Distance/Bearing is used than the first point of the polygon(s) must have coordinates i.e. the first point cannot be relative to e.g. the Benchmark.
Using Data Entry Forms
Retrieved from "http://mwiki.gichd.org:8090/mediawiki/index.php?title=Import_Geospatial_Data_from_Excel&oldid=23954"