1 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Location grid squares

Hi.

I am trying to correct a problem relating to an old bug - now fixed but some of the data affected needs correcting. This applies to the location grid squares which had a blank value for SPATIAL_REF_SYSTEM - the blank values are no longer created, but those that were already in place are still there causing error messages.

The plan is to add a map layer to automatically re-extract grid squares from, hopefully getting a complete set of squares across all properties. First I need to clear the existing squares for all relevant locations, those with the LOCATION_TYPE' of 'Site' - or alternatively all those at level 2 of my location hierarchy, but not sure if this approach is possible.

Can anyone give me some pointers on this, as a newbie to the Management Studio?

Thanks

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: Location grid squares

Gordon

The following  SQL will delete the Grid squares in the way you specify assuming that by 'Site' you mean the system supplied Location_type. As a precaution  put your own site Id in the query to stop it deleting system supplied data or other imported data.  (there may not be any). This query doesn't look at the level in the Location hierarchy, which would be slightly more complicated to do.   If you copy this into a new query in Management studio Express, change the site id (needs to be in single quotes)  and execute it then this will remove the specified Locations. If you do try this then take a back up first and check the results before continuing to use Recorder.   
   
Delete from grid_square
FROM Grid_square GS
INNER JOIN Location L ON L.Location_Key = GS.Location_key
WHERE Location_Type_key = 'NBNSYS0000000005'
AND GS.Custodian = 'Your Site id'


The following would just correct the Spatial_ref_system problem, assuming you just use OSGB.

Update Grid_Square
Set Spatial_ref_System = 'OSGB' where spatial_ref_system is null 

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Location grid squares

Thanks Mike,

Will have a go with that. There shouldn't be any 'Sites' at a lower level as they should all be 'Sub-sites', but a few will have slipped through and I will just have to remeber to check it on the next stage. Some are in N Ireland, so the other bit wouldn't work without a query first to extract those with odd/even numbers of characters in the gridref or on the first letter of the ref. Deletion and re-extraction should be fine and hopefully ensure I get a complete set of squares which may not have been the case with previous workarounds.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

4 Reply by Gordon Barker (edited by Gordon Barker 03-08-2010 09:39:28)

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Location grid squares

Seems to have worked fine, a follow up question though. Having attached new boundaries to locations, I have spotted that there are some older boundaries attached which probably relate to files that were in the previous installation. This seems to cause errors using Find on Map resulting in the following message extract:

exception class   : EOleException
exception message : Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

to solve  this I presumably need to clear the Location_Boundary of old associations with the following SQL (40391 being 1/8/10)

DELETE FROM LOCATION_BOUNDARY
WHERE     (FROM_VAGUE_DATE_START < 40391)
AND LOCATION_BOUNDARY.CUSTODIAN = "DSS00046"
Gordon Barker
Biological Survey Data Manager
National Trust