Loading Aerial Photos with ECW or TIF files

Hi Jerry

TEST ENVIRONMENT
On further trying to sort this out, I found that it did not matter which shape file I loaded first in the Form_Load event, it would always return with -1. To get around this I just loaded the first layer and straight after tested if it was -1 and then loaded again - this time it would load correctly in the correct place and all other layers (shape files) loaded correctly as well, except the layers from the SQL 2014 database that still do not load and gave the error - Latitude or Longitude exceeded limits

LIVE ENVIRONMENT
If I load the first layer as an SDE layer, it loads at the incorrect place. All further sde layers also load at the wrong place. However, if I load the first layer as a shape file with a .prj file then it loads at the correct place and all sde layers after this also load at the correct place. The SQL 2014 layers still do not load.

I will now try to add the dbo.geometry_columns table and let you know what happens.
I will also puzzled by the srid values. Noone is available to ask about this until the 11 th Jan

Regards
Colleen Crawford

Hi

I found the 2 files in my SQL 2014 database - don’t know how I missed them. They must have been created when I added the shape columns. I will look into this tomorrow and see what I need to add to both files.From your reply it seems you need to add this data manually yourself.

Regards

Colleen

Yes, you will have to add it.

Since it’s an OGR construct, SQL Server knows nothing about the data. If you were using one of the GDAL tools, for instance, ogr2ogr, to convert/import a Shapefile into SQL Server, I think it would do all of that for you, but since you are adding the spatial columns to existing tables, you are responsible to add the metadata. But it’s not difficult.

Hope it goes well.
Jerry.

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

1 Like

Hi Jerry

I have made a bit of headway on this. The GIS dept seems to have given me an incorrect path. I have corrected and find the following

From their notes section

It provides clients a map of where tiles exists in a bundle, so that they can make requests only for the tiles that exist reducing unnecessary traffic to the service and hence improving the performance. The tilemap request returns a JSON structure containing tile presence information for an area of a specific level. The tilemap request uses the following syntax:

http://map-image-vectorservice-url>/tilemap/////

The level, row, and column arguments select the level and the top-left tile location, while the width and height specifies the size of the requested area.

Eg

https://basemapsdev.arcgis.com/arcgis/rest/services/World_Basemap_Export_v2/VectorTileServer/tilemap/5/0/0/8/8

I tried a number of variations

http://w8vmgis01.durban.gov.za/arcgis/rest/services/WebViewers/AerialPhotography2019/ImageServer/12/1200/1200.png

When I copy the url and paste, this is what it pastes

Raster Catalog Item (ID: 12) (durban.gov.za)

This is what is displayed

Then I tried going into the json code by using ?f=pjson at the end of the URL – did not work

If I click on Raster Image I get

Path is http://w8vmgis01.durban.gov.za/arcgis/rest/services/WebViewers/AerialPhotography2019/ImageServer/12/image?bbox=3426765.132100001,-3455942.771299999,3427916.6537000015,-3454785.3852999993

But copy/paste displays the following

Raster Image (ID: 12) (durban.gov.za)

I can’t use tilemap as it gives an error

When I use http://w8vmgis01.durban.gov.za/arcgis/rest/services/WebViewers/AerialPhotography2019/ImageServer/12/1200/1200.png

It seems to be going to a valid image but the zoom parameter takes me to that catalogue No.

I am not sure how to make the image display. When I run it in the program I do not get an error but nothing is displayed

Regards

Colleen

This is an old and long thread.
I’m closing it.

If you still have questions, please re-post.