Selecting the first record from grouped records

I need to create a table of localities with the number of waste bins associated with each, however, the address table can have multiple localities for a single property number. Therefore, rather than skew the results by counting a single bin twice (or more) for each locality the property has an address in it is better to simply select the first locality in the list of address and assign that to the bin count.

For example:

select b.property_address_2 as locality,

sum (a.number) as bin_count

from rd_waste as a

join

(select distinct property_number,

property_address_2,

row_number() over (partition by property_number order by property_address_2) as record 

from rd_address) as b

on a.property_number = b.property_number

where a.description like ‘%Waste%’

and b.record = 1

group by b.property_address_2

order by b.property_address_2

Automating geometry_columns extents

I think this sets a record for becoming irrelevant in the shortest space of time… the most recent QGIS 2.0 dev-build has been changed so that it automtically calculates the extents of datasets in the geometry_columns table without the need for the extra columns. The dangers of dealing with actively developed open source!

Having populated min_x, min_y, max_x and max_y columns (all int/null) in the geometry_columns table in SQL Server (automatically created when using ogr2ogr) creates huge time savings when loading SQL Server tables into QGIS 2.0.

To add these column to geometry_columns, use the following SQL:

ALTER geometry_columns
ADD min_x int null,
min_y int null,
max_x int null,
max_y int null

The stored procedure below automatically populates the extent values of all records in the geometry_columns table. It is currently set to only populate records with a particular SRID. Either change the SRID to the appropriate value or remove altogether:

CREATE PROCEDURE usp_extentsUpdate

AS

DECLARE @tablename VARCHAR(MAX)
DECLARE @srid INT = 28355

BEGIN

SET NOCOUNT ON;
DECLARE extents_cursor CURSOR FOR
SELECT f_table_name
FROM geometry_columns
WHERE srid = @srid

OPEN extents_cursor;

FETCH NEXT FROM extents_cursor
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sql VARCHAR(MAX)
SET @sql = ‘update geometry_columns set min_x = a.minx, min_y = a.miny, max_x = a.maxx, max_y = a.maxy from (select ‘ + ”” + @tablename + ”” + ‘ as table_name, MIN(ogr_geometry.STPointN(1).STX) as minx, MIN(ogr_geometry.STPointN(1).STY) as miny, MAX(ogr_geometry.STPointN(1).STX) as maxx, MAX(ogr_geometry.STPointN(1).STY) as maxy from ‘ + @tablename + ‘) as a where f_table_name = a.table_name’

EXEC (@sql)

SELECT @tablename + ‘ completed’

FETCH NEXT FROM extents_cursor
INTO @tablename
END
CLOSE extents_cursor
DEALLOCATE extents_cursor
END

Find my nearest…

I currently use the following method for calculating the nearest ‘thing’ to a point of interest:  http://blogs.msdn.com/b/isaac/archive/2008/10/23/nearest-neighbors.aspx, my thanks to Isaac Kunen!

With some slight modifications…

This stored procedure calculates the distance from the centroid of a parcel to the closest fire plug, an important measurement when subdividing land to ensure all part of a new property can be reached by a fire hose!

Some notes:

  • @XPoint and @YPoint are parcel centroid parameters passed to the stored procedure in the form of UTM coordinates
  • When creating the geometry using the point parameters it is important to get the SRID correct, this caused a minor headache for a while until I figured out that I needed to use 28355 (Zone 55)
  • A full explanation of the SQL used here can be found in the referenced web page as well as the SQL needed to create the ‘numbers’ table that is used to iterate the search area
  • This SQL is used as part of our Online Property Report mapping service: http://maps.alpineshire.vic.gov.au/opr/

CREATE PROCEDURE [dbo].[usp_GetFirePlugDist]

@XPoint VARCHAR(100),
@YPoint VARCHAR(100)

AS

DECLARE @Point VARCHAR(100)
DECLARE @start FLOAT = 1000;
DECLARE @x GEOMETRY;

SET @Point = ‘POINT(‘ + @Xpoint + ‘ ‘ + @Ypoint + ‘)’

SET @x = GEOMETRY::STGeomFromText(@Point, 28355);

WITH NearestPoints AS
(
SELECT TOP(1) WITH TIES *, Round(b.ogr_geometry.STDistance(@x), 0) AS DISTANCE
FROM numbers AS a
JOIN fire_plugs AS b
ON b.ogr_geometry.STDistance(@x) < @start*POWER(2,a.n)
ORDER BY a.n
)
SELECT DISTINCT TOP(1) DISTANCE AS distance_metres
FROM NearestPoints

GO