Hi Jerry
At last I have sorted this out. I am posting my notes here to help others
HOW TO LOAD SHAPES TO SQL
Add Points
In order to add points to SQL Database – add a geometry column to the table.
Create Geometry field on LocnInfo and update as follows using x/y co-ords – 32735 is our SRID
USE AccidentSuite
Update LocationInfo Set LocnPtShape = geometry::STPointFromText(‘POINT(’+ str([x_Coord]) + ’ ’ + str([y_Coord]) + ‘)’, 32735)
Add Lines
In order to add Lines to SQL Database from shape file – add a geometry column to the table. Run ShapeToSQL
The following 2 tables are required. They were automatically created for me when I ran ShapeToSQL, but you can create them manually
geometry_columns
spatial_ref_sys
Code to create table
CREATE TABLE [dbo].[geometry_columns](
[f_table_catalog] varchar NOT NULL,
[f_table_schema] varchar NOT NULL,
[f_table_name] varchar NOT NULL,
[f_geometry_column] varchar NOT NULL,
[coord_dimension] [int] NOT NULL,
[srid] [int] NOT NULL,
[geometry_type] varchar NOT NULL,
CONSTRAINT [geometry_columns_pk] PRIMARY KEY CLUSTERED
(
[f_table_catalog] ASC,
[f_table_schema] ASC,
[f_table_name] ASC,
[f_geometry_column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
To populate geometry_columns table run this code and you will find most of the fields. Add the fields to the geometry_columns table
select * FROM information_schema.columns where data_type = ‘geometry’
Catalog – database name eg AccidentSuite
Schema – owner name eg dbo
Table_name – table that has the shape eg LocationInfo
Geometry_column – the name where the shape has been stored eg LocnPtShape
Coord_dimension – normally 2
Srid – eg 32735
Geometry_type – eg POINT, LINE, POLYGON
Spatial_Ref_Sys – Use SRID that you create shapes with and for the srText I copied the text from the .prj for shapefiles used by our system.
Code to create table
CREATE TABLE [dbo].[spatial_ref_sys](
[srid] [int] NOT NULL,
[auth_name] varchar NULL,
[auth_srid] [int] NULL,
[srtext] varchar NULL,
[proj4text] varchar NULL,
PRIMARY KEY CLUSTERED
(
[srid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
srid auth_name auth_srid srtext
32735 EPSG 32735 PROJCS[“South Africa wg31”,GEOGCS[“GCS_WGS_1984”,DATUM[“D_WGS_1984”,SPHEROID[“WGS_1984”,6378137.0,298.257223563]],PRIMEM[“Greenwich”,0.0],UNIT[“Degree”,0.0174532925199433]],PROJECTION[“Transverse_Mercator”],PARAMETER[“False_Easting”,0.0],PARAMETER[“False_Northing”,0.0],PARAMETER[“Central_Meridian”,31.0],PARAMETER[“Scale_Factor”,1.0],PARAMETER[“Latitude_Of_Origin”,0.0],UNIT[“Meter”,1.0]]
proj4text
+proj=utm +zone=35 +south +ellps=WGS84 +datum=WGS84 +units=m +no_defs
NB: Don’t forget to give these tables the same permissions as the tables that have the shapes