How To: Create a new ArcSDE layer using shp2sde
Instructions provided describe how to use a world shapefile, included with ArcView, to load SDE binary data into the center of the ArcSDE integer coordinate space, which has a fixed extent of 2147483647 wide and 2147483647 high.
- Locate the ArcView sample data shapefile cntry92.shp. It may be at: \esri\esridata\world\cntry92.shp.
- Start the ArcSDE service.
- Open a Command Prompt window and run shp2sde as follows; the parameters do not need to be in a particular order:
For Oracle, DB2, Informix:
Code: shp2sde –o create –l world_data,shape –f C:\esri\esridata\world\cntry92.shp –g 38 –x –9646,-9649,111319 –e a+n –a all –c 2000 –v –i esri_sde –s computer1 –u username –p passwordFor SQLServer:
Code: shp2sde –o create –l world_data,shape –f C:\esri\esridata\world\cntry92.shp –g 38 –x –9646,-9649,111319 –e a+n –a all –c 2000 –v -D database –i esri_sde –s computer1 –u username –p passwordModify the above parameters as needed:
–f the pathname for the shapefile -D the name of the database (SQLServer only) -i the name of your ArcSDE service (default name is esri_sde) –s the name of the server -u the RDBMS SDE username -p the RDBMS SDE passwordHow to calculate the -x parameter used in the examples above.
-x This parameter is optional. If this parameter is not used, ArcSDE determines its own X and Y offsets and XYScale to load the data. This may not be desirable if data is appended to the layer later, since ArcSDE does not automatically center the data in the ArcSDE coordinate integer space. This parameter is only used with shp2sde –o create. There are three parts to this parameter: X Offset, Y Offset, and XYScale, in this order. Select an XYScale that supports the data’s resolution, and perhaps allows later features to be added with a higher resolution. A large XYScale does not increase the quality of the data, it only allows higher resolution data to be stored later.
Warning: An XYScale that is too small can greatly generalize the data, whereas too large a scale may not fit in the available ArcSDE coordinate integer space. Both could result in the data not loading, or not allowing for future growth.The following is how the –x values of –9646, -9649, and 111319 were determined:
Note: The following steps are based on the example data with units in decimal degrees. The steps are the same with data in other units, such as meters or feet. Knowing the units of the data is essential to obtaining correct values.A. Run the shpinfo command with the proper path to the shapfile on the system for the -f parameter to view the attributes of the shapefile:
Code: shpinfo –o describe –f C:\esri\esridata\world\cntry92.shpB. Get the minX, minY, maxX, and maxY extents of the shapefile from the output returned by shpinfo. For example: -180, -90, 180, 83.596.
C. Determine an XYScale for 1-meter resolution (1 meter is used as an example).
The circumference of the Earth at Equator in meters equals 40075017. The Number of Degrees around the earth is 360 (remember that the data is in decimal degrees). XYScale = 40075017 / 360 = ~111319D. Test if the data fits in the ArcSDE integer coordinate space of 2147483647 by 2147483647.
Width of data = 180 – (-180) = 360 Height of data = 83.596 – (-90) = 173.596 Multiply width and height by XYScale (111319) 360 * 111319 = 40074840 173.596 * 111319 = 19324533.124If both numbers are less than 2147483647, the data fits and the XYScale of 111319 can be used.
E. Find the center of the ArcSDE coordinate integer space. Both width and height are the same, so there is only a need to divide once.
2147483647 / 2 = 1073741823.5F. Convert the value to decimal degrees by applying the XYScale.
1073741823.5 / 111319 = 9645.6294G. Calculate the center of the shapefile.
minX + (Width of data / 2) = -180 + 180 = 0 minY + (Height of data / 2) = -90 + 86.798 = -3.202H. Determine X Offset and Y Offset.
X Offset = x center of shpfile - center of ArcSDE integer space Y Offset = y center of shpfile - center of ArcSDE integer space X Offset = 0 – 9645.6294 = ~ -9646 Y Offset = -3.202 – 9645.6294 = ~ -9649Results: XOffset -9646, YOffset -9649, XYScale 111319.
Note: The XYScale or Offsets cannot be altered once the data is loaded.
Note:It is recommended to round off values for the -x parameters.
How to calculate the -g parameter used in the example above.
-g is a required parameter for 8.0.1 and 8.0.2. It is optional for 8.1. Grid size is used for spatial indexing; it can greatly affect performance. Unfortunately, there isn't a perfect algorithm for determining an optimal grid size. Because the grid size can be changed after the data is loaded (sdelayer –o alter�), trial and error may result in a better grid tile size. Consider these guidelines:
· Grid tile size is in the same units as the data.
· Tile size should not be smaller than an average feature envelope size.
· Grid tile size should be at least three times larger than the average feature envelope to keep the spatial index table from growing too large. If feature envelopes vary greatly (many tiny feature envelopes mixed with huge feature envelopes), the –g option can be set for up to three grid levels.
For additional information about Spatial Indexing, see the config_tuning_guide_<rdbms>.pdf file included with ArcSDE, which can be found in the SDEHOME\documentation directory.
Note: Point data may be best approached by trial and error, because points do not have any spatial extent (spatial envelope). Start by using a very small grid size (0.1 for decimal degree data, or 1 for projected data), and alter it as needed.The following information describes how the example value of 38 was determined:
A. Before loading the world data into ArcSDE, load the shapefile into ArcView to calculate the average feature envelope.
B. Multiply it by 3, as recommended in the guidelines above.
C. With the cntry92.shp file theme activated in a View window, do the following:
- Click Theme Menu > Table. This opens the themes attribute table.
- Click Table Menu > Start Editing.
- Click Edit Menu > Add Field.
- For Name, type Extent.
- Click OK.
- Click Field Menu > Calculate.
- In the Text Window type:
Code: ( [Shape].ReturnExtent.GetWidth + [Shape].ReturnExtent.GetHeight ) / 2.
- Click OK.
- Click Table Menu > Stop Editing.
- Save the edits when prompted.
- Click Field Menu > Statistics.
- Take the average mean and multiply it by 3. The result should be approximately 38.
Note: It is recommended to round off values for the -g parameter.Finding a grid size, as illustrated above, may only be a starting point. A grid size of 38 works, but it may be necessary to alter the size (use sdelayer –o alter) to increase performance results.
ArcSDE can display the results of our grid size by using the following command on the new ArcSDE layer.
Code: sdelayer –o si_stats –l world_data,shape –u username –p password –i esri_sde
Note: For details on sdelayer -o si_stats, and the information it returns, refer to the SDEHOME/documentation/config_tuning_guide_<rdbms>.pdf file.Explanation and information about the rest of the shp2sde parameters used in the examples above.
-l This parameter (lower case L) is required and asks for a table and a column. The table name must conform to the RDBMS naming conventions for tables. The table name is also the ArcSDE layer name being created. In the example, the layer or table name is world_data.
Shape is often used for the column name, and is recommended.
-e This optional parameter is used for entity types that are allowed for the SDE layer being created. Multi-part and nil shapes are automatically allowed for shapefiles. The following list includes the + and n entity codes:
Point = pn PointM = pMn PointZ = p3Mn polyline = s+n polylineM = s+Mn PolylineZ = s+3Mn polygon = a+n polygonM = a+Mn polygonZ = a+3Mn Multipoint = p+n multipointM=p+Mn multipointZ = p+3Mn
Note: ArcSDE allows layers to have multiple entity types, similar to coverages (point, line and polygon shapes may all be part of one layer). ArcGIS applications, such as ArcMap and ArcCatalog, do not support multiple entity SDE layers, and they will not be available when connecting to ArcSDE with ArcGIS clients. The feature types above are not multiple feature types. Polygon is shown using an 'a' for area, an 'n' for nil, and a '+' for multipart (a+n). If 'p' for point is included (pa+n), the layer becomes a multiple entity layer that allows points and polygon features to exist in the same layer.
-a This optional parameter determines the attribute modes: 'all' includes all attributes from the shapefile and 'none' does not load any attributes. It is possible to use 'file=<file_name>' to specify a file that maps the columns from the shapefile onto the new database columns of the business (attribute) table. For example, the file could look something like this:
name country_name abbrevname abbreviation fips_code fips wb_cntry cntry_code
In this example, only the columns named on the left would be imported into the resulting business (attribute) table and those columns are renamed with the names on the right during the process.
-c This optional parameter is used to set the commit rate of features being loaded to the database. The value of 2000 is arbitrary. It is primarily used to reduce the time it takes to load data by committing data to the database in increments. The use of this parameter effectively overrides the AUTOCOMMIT parameter found in the SDEHOME/etc/giomgr.defs file, which is initially set to 1000 on installation of ArcSDE. When loading data, it is possible to increase the number of transactions that the RDBMS can process before performing a commit with this option. This speeds up the data loading process.
-v This optional parameter allows the user to see how the features are being committed during the load by echoing information back to the screen.
-D This optional parameter is only used for SQLServer and is the database name or data source name. If this parameter is not used, the data will be loaded into the default database.
-i This optional parameter is the name of the user's ArcSDE service. Use of this parameter is only necessary if the name of the user's service is not esri_sde (the default). The name of the user's ArcSDE service can be found in the %SDEHOME%\etc\services.sde file.
-s This optional parameter is the name of the server or machine name that ArcSDE is installed on. If the shp2sde command is being run on the machine (server) that ArcSDE is installed on, the parameter is not needed.
-u This required parameter is the RDBMS user who will own the resulting ArcSDE layer. This RDBMS user should have already been granted the CONNECT and RESOURCE permissions. It is strongly recommended not to load the data as the SDE user, since the SDE user is the administrator of ArcSDE.
-p This required parameter is the password for the above RDMBS username.
Note: The example above demonstrates one approach to loading a world shapefile into an RDBMS by way of ArcSDE. Because many factors should be considered when loading data into an RDBMS, it is strongly recommended to enroll in ESRI’s Introduction to ArcSDE and ArcSDE Administration courses to handle individual data storage and performance issues.
Additional information is available in the Related Information below.