Category Archives: Uncategorized

Spatially index (*.qix) all shapefiles in a folder (and sub-folders)

pushd D:\<folder>

for /R %%f in (*.shp) do ogrinfo -sql “create spatial index on %%~nf” “%%f”

Point the function at the correct folder. Loop recursively through the folder/sub-folder structure looking for shapefiles then undertake an ogrinfo SQL command on each file. %%nf provides the file name and %%f provides the full path to the file. Double ‘%%’ is required when the commands are run from a batch file, otherwise single ‘%’ will do.

Splitting delimited text in SQL

Split delimited text:

declare @userinput varchar(100) = ‘GREAT ALPINE ROAD – BRIGHT’
declare @roadname varchar(100)
declare @locality varchar(100)

select
@roadname = rtrim(PARSENAME(replace(@userinput, ‘-‘,’.’),2)),
@locality = ltrim(PARSENAME(replace(@userinput, ‘-‘,’.’),1))

declare @geo geometry

select @geo = ogr_geometry
from [GISProduction].[dbo].[locality_polygon]
where locality = @locality

select pfi
from [GISProduction].[dbo].[tr_road] as a
where a.ogr_geometry.STIntersects(@geo) = 1
and a.ezi_rdname = @roadname