1 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: What happens to records without Locations assigned

I've got available in Recorder a couple of methods of interrogating complex regions:

1. Using the Polygon restriction in the Wizard (which we've seen recently can only work for rectilinear polygons and therefore not for administrative boundaries)
2. Setting up a structured Location hierarchy then writing XMLs which recurse down the hierarchy from a selected parent Location (e.g. a parish), picking up the Location identifiers of all the children, storing them into a temporary table and then using this to interrogate for what I want (e.g. areas of Local Wildlife Sites or BAP habitats, full taxon lists for a parish - I've written many of these)

My question is:
If I import records without giving them a Location name (such that the Event just shows date and Grid reference), does my second method pick these up?

2 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: What happens to records without Locations assigned

Hi Darwyn

In answer to your question, no these are not picked up if you write an XML report to do this. Creating an observation within the known boundary of a location does not automatically link the observation to that location so a simple query like this would not work, you'd need a spatial query.

Also, in response to point 1 its not that the spatial query function in the wizard does not cope with non-rectilinear polygons, it will cope with any shape polygon even very complex ones. The issue is that the way it determines inclusion in the polygon for grid squares is to test the bottom left corner of the grid square against the polygon, if that is in then the square is counted as in. A more powerful GIS approach is to test the percentage of the whole grid square that lies inside the polygon, if its over a certain amount then the square is considered inside. Of course neither of these approaches are perfect. The problem with implementing the second approach is that I believe the algorithm will be quite complex and therefore slow, but if anyone can provide guidance on how to do this test in a very efficient way on thousands of records I'd be interested to hear!

Cheers

John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by davec (edited by davec 17-08-2007 09:40:29)

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: What happens to records without Locations assigned

Hi Jon,

Are you aware of this for Delphi?

http://www.tatukgis.com/products/Dk/kernel.aspx

The operations we need (buffers, intersections) could also be accomplished by calling out to the open source library GDAL/OGR. The problems, as I see it, is that Recorder/SQL Server does not have the framework, as yet, to handle the storage of GIS features. AFASIK, SQL Server would have to have the spatial extension add on (I think it quite expensive to purchase) and I guess this would not be available to MSDE users.

To help further it would be interesting to know how Recorder implements the point in polygon test you mention above in the report wizard. I'm a bit confused as I was sure Recorder already had a Delphi spatial library, but not having access to Recorder 6 until last week, I dont know what's what.

There are a number of techniques we could use to include point features which are in the SW corner of a square at some determinate distance from a polygon feature. One  is to extend the bounding box of the polygon to capture any points which may be in the region of interest. For 10km resolution points make the bounding box at least 15km in each direction. This is a fast spatial operation.

For those points captured with a resolution of e.g. 10km add 5km to each axis to move it into the center of the square. For each of these features generate a bounding box of 10km. Run another spatial test to see if any of these bounding boxes touch/intersects with the polygon and factor out as required. This is a slower operation and without spatial indexes (e.g. GIST) could be quite (nay very) slow for large data.

At this point can I just sink my head in my hands and say I wish were using Postgresql with PostGIS - but that would not be very helpful to the problem at hand would it :)

Kind Regards,

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

4 Reply by davec (edited by davec 17-08-2007 10:35:27)

  • davec
  • Veteran
  • Offline
  • From: Brecon, Powys, Wales
  • Registered: 11-05-2006
  • Posts: 108

Re: What happens to records without Locations assigned

Here is a computational geometry library for Delphi;

http://www.partow.net/projects/fastgeo/index.html

I've not got time (I'm on leave at the moment) to review it in detail. One aspect to note is the coordinate system used in the library. It may use linear 2D space, in which case we would need to be aware of re-projections to/from e.g. OSGB or Lat/Lon WGS84. See also http://www.remotesensing.org/proj/ where there is a Delphi port of Proj4 projections library,

Hope this helps.

Kind Regards.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

davec's Website

5 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: What happens to records without Locations assigned

Thanks gentlemen.
In reply to Jon's last observation about complexity, here's a few figures.
In a database of 21,302 Locations, the following code (this one is just searching Designation information in Locations and has no link to taxa) gave me a Report screen in 18s and full list in 35s, returning 5,379 records:
HAP areas in Location

<?xml version="1.0" ?> 
<CustomReport menupath="Location Reports" title="Habitat designations for a location hierarchy" description="Generates a list of designations and their areas." template="HAP areas.Tpl">
<SQL>
--Darwyn Sumner based upon Mike Weideli
CREATE TABLE #Locations (
  Location_Key CHAR(16) COLLATE Database_Default PRIMARY KEY,
  Parent_Key CHAR(16)  COLLATE Database_Default
)

CREATE TABLE #Parent (
  Location_Key CHAR(16) COLLATE Database_Default PRIMARY KEY,
  Parent_Key CHAR(16) COLLATE Database_Default
)

<Where keytype="Location">

INSERT INTO #Parent VALUES ('%s', 'B')

INSERT INTO #Locations Select #Parent.Location_Key, 'B' FROM #Parent

WHILE 1=1 BEGIN
    INSERT INTO #Locations
        SELECT L.Location_Key, 'B'
        FROM Location L
        INNER JOIN #Locations Tinc on L.Parent_Key=TInc.Location_Key
        LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key
        WHERE Texc.Location_Key IS NULL
    IF @@ROWCOUNT=0
        BREAK
END

UPDATE #Locations Set Parent_Key = #Parent.Location_Key
FROM #Parent
    INNER JOIN #Locations ON #Locations.Parent_Key=#Parent.Parent_Key

SELECT distinct
    LOCATION.LOCATION_KEY, 
    SITE_STATUS.SHORT_NAME AS Status, 
    LOCATION.FILE_CODE AS Object, 
          LOCATION_NAME.ITEM_NAME AS Location, 
          LOCATION.SPATIAL_REF AS GridRef, 
          LOCATION_DESIGNATION.REF_CODE AS FilePlanID, 
          LOCATION_DATA.DATA AS Area
          --List_of_locations.LOCATION_KEY AS [Source key]
    --LOCATION_NAME.ITEM_NAME, 
    --convert(float (8),(LOCATION_DATA.DATA)) AS Float
    --sum(convert(float (8),(LOCATION_DATA.DATA))) AS Total,
    --Count(LOCATION.LOCATION_KEY) AS NumberLoc
    --sum(floats) as FTotal
    --Not solved by sending to Report - no calculated fields
FROM 
    LOCATION_DESIGNATION LOCATION_DESIGNATION_1 INNER JOIN
          LOCATION INNER JOIN
          LOCATION_DATA ON LOCATION.LOCATION_KEY = LOCATION_DATA.LOCATION_KEY INNER JOIN
          LOCATION_DESIGNATION ON LOCATION.LOCATION_KEY = LOCATION_DESIGNATION.LOCATION_KEY INNER JOIN
          LOCATION_NAME ON LOCATION.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY INNER JOIN
          SITE_STATUS ON LOCATION_DESIGNATION.SITE_STATUS_KEY = SITE_STATUS.SITE_STATUS_KEY INNER JOIN
          MEASUREMENT_TYPE INNER JOIN
          MEASUREMENT_UNIT ON 
          MEASUREMENT_TYPE.MEASUREMENT_TYPE_KEY = MEASUREMENT_UNIT.MEASUREMENT_TYPE_KEY INNER JOIN
          MEASUREMENT_QUALIFIER ON 
          MEASUREMENT_TYPE.MEASUREMENT_TYPE_KEY = MEASUREMENT_QUALIFIER.MEASUREMENT_TYPE_KEY ON 
          LOCATION_DATA.MEASUREMENT_QUALIFIER_KEY = MEASUREMENT_QUALIFIER.MEASUREMENT_QUALIFIER_KEY ON 
          LOCATION_DESIGNATION_1.LOCATION_KEY = LOCATION.LOCATION_KEY INNER JOIN
          SITE_STATUS SITE_STATUS_1 ON LOCATION_DESIGNATION_1.SITE_STATUS_KEY = SITE_STATUS_1.SITE_STATUS_KEY RIGHT OUTER JOIN
          [#Locations] ON LOCATION.LOCATION_KEY = [#Locations].Location_Key    
    --LOCATION_NAME INNER JOIN ((([#Locations] INNER JOIN 
    --LOCATION ON [#Locations].Location_Key = LOCATION.LOCATION_KEY) INNER JOIN 
    --SAMPLE ON LOCATION.LOCATION_KEY = SAMPLE.LOCATION_KEY) INNER JOIN 
    --TAXON_OCCURRENCE ON SAMPLE.SAMPLE_KEY = TAXON_OCCURRENCE.SAMPLE_KEY) ON LOCATION_NAME.LOCATION_KEY = [#Locations].Parent_key
    

WHERE 
    (((LOCATION_NAME.PREFERRED)=1))
GROUP BY 
    LOCATION.LOCATION_KEY, 
    SITE_STATUS.SHORT_NAME, 
    LOCATION.FILE_CODE, 
    LOCATION_NAME.ITEM_NAME, 
        LOCATION.SPATIAL_REF, 
        LOCATION_DESIGNATION.REF_CODE, 
        LOCATION_DATA.DATA, 
        MEASUREMENT_TYPE.SHORT_NAME, 
        MEASUREMENT_UNIT.SHORT_NAME,
        [#Locations].Location_Key

HAVING  
    (MEASUREMENT_UNIT.SHORT_NAME = 'hectares') 
    AND 
    (MEASUREMENT_TYPE.SHORT_NAME = 'Area') 
    --AND 
        --(SITE_STATUS.SHORT_NAME = 'Wildlife Site A')
        --(SITE_STATUS.SHORT_NAME = 'H15')

</Where>
                                        
</SQL>
    <Columns>
    <Column name="Location_key" position="1" caption="Location_key" visible="True" width="110"/>
    <Column name="Status" position="2" caption="Status" visible="True" width="42"/>
    <Column name="Object" position="3" caption="Object" visible="True" width="45"/>
    <Column name="Location" position="4" caption="Location" visible="True" width="200"/>
    <Column name="GridRef" position="5" caption="Grid" visible="True" width="70"/>
    <Column name="FilePlanID" position="6" caption="FilePlanID" visible="True" width="100"/>
    <Column name="Area" position="7" caption="Area" visible="True" width="50"/>
    </Columns>
</CustomReport>

(the remmed stuff is a failed attempt to get the thing to add up areas from specific Designations such as Local Wildlife Sites - any solutions would be gratefully received, I have to finish the job off by exporting to an Excel file linked to queries inside Access, others might use Snapshot)
This is very much the sort of question one is likely to be asked as it's a main figure for Local Development Framework.

My opening topic is linked to another on this site in "Missing records from polygon search" in the Troubleshooting section.