Subscribe to this thread
Home - General / All posts - Alter table SQL problem in 9
Graeme

990 post(s)
#21-Mar-18 02:12

I Have downloaded sample Geoscape data from PSMA. Loaded the geodatabase verion into 9 - works great. There's only one table with an area geom called "SA_BUILDING_POLYGON". I copied this code by ADAMW from a forum thread

--SQL9

ALTER TABLE [Products] (ADD [CategoryName] NVARCHAR AS (

  SELECT [_CategoryName] FROM [Categories]

  WHERE [_CategoryID] = [CategoryID]

));

but can't find it again, and modified to add two columns from two other tables to the drawing table (we'd use a new table relation in 8 context). I wanted to map swimming pools - there isn't a spatial layer available for pools. The only indirect route was to use the added "HAS_SWIMMING_POOL_ADJACENT" for the building_polygons. Unfortunately the Building_PID includes shed and other outbuildings so there can be many buidings adjacent to a given pool - over-counting on the objective of a pool count. There is a table "SA_BUILDING_ADDRESS" with the unique Building_PID plus a "ADDRESS_DETAIL_PID" which offers the potential to group the multi building areas by it. I duplicated and edited the successful local version of the query to add a new column containing the "SA_BUILDING_ADDRESS" based on a join on Building_PID (I made a copy of the Building_PID to keep the name unique). The query runs, returns an added column, but it is populated with <NULL>. Apart from being a different data type (navchar instead of Int32) and a different table (all double clicked from the query builder to avoid typos) it's the same as the first two added columns. I've attached a cut down mxb just with the participating data. Where am I going wrong please?

I exported the relevant drawing as a mif and the required table with addresses as a csv. Imported them to 8, new table relation, on Building_PID, related address field; drawing / dissolve on address; centroids_inner on the now branched polygons for a usable result. I'd like to be able to do this sort of thing in 9 as I suspect the target national data set, rather than this small sample, may overwhelm 8, as well as getting up a bit more speed with 9.

I actually tried to copy the participating components into a new 9 project, but when I copied the main "SA_BUILDING_POLYGON" table and hit paste in the new project pane I got an "invalid object reference" Manifold error - guessing this is because of the added fields? the mxb has had the unused components deleted, which probably took a bit longer than a copy / paste into a new project would.

Attachments:
Geoscape trial data query problem.mxb

tjhb
10,094 post(s)
#21-Mar-18 02:26

Graeme,

Attempting to open the attached .mxb gives "Invalid object reference".

Graeme

990 post(s)
#21-Mar-18 02:31

I hadn't tested it Tim, sorry. I must have deleted something which is required as I can't open the .map version either. I'll try again and, might take a while!

Graeme

990 post(s)
#21-Mar-18 02:43

I've zipped up the 9 map - tested. I couldn't get an mxb to re-open, tried creating from both 165.5 and 165.0

Attachments:
Geoscape trial data query problem.zip

adamw


10,447 post(s)
#23-Mar-18 13:19

The 'Invalid object reference' when opening the MXB is our issue (a computed field referring to a different table may sometimes fail to load, this then fails the entire operation).

We will fix it.

tjhb
10,094 post(s)
#21-Mar-18 02:31

I'm not sure if I follow fully, but how about combining all buildings for the same address (GeomMergeAreas, grouping by common address ID), and taking the maximum value of HAS_SWIMMING_POOL_ADJACENT for each address (each combined group of buildings)?

Graeme

990 post(s)
#21-Mar-18 02:33

The drawing (has a geom) table doesn't have an address field. That's what I need to add via alter table (old 8 reation)

tjhb
10,094 post(s)
#21-Mar-18 02:40

So you can add that using a join and an UPDATE query?

OK, that's what you're trying to do, and it's failing.

Graeme

990 post(s)
#21-Mar-18 02:46

That's correct. Edited versions of Adams query in the first thread worked fine for two separate integer fields in two different tables. The third edited version, to add the address navchar field from a third table returns all <null>.

tjhb
10,094 post(s)
#21-Mar-18 03:02

Adam's query (designed to approximate a relation) is not the right approach here, nice try but it just complicates things.

Here is what I understand:

Table A is address data, with fields BUILDING_PID and ADDRESS_DETAIL_PID. It has no geometry.

Table B is building outlines, with fields BUILDING_PID and HAS_SWIMMING_POOL_ADJACENT (1-> yes, 2-> no). Its geometry is shown by a drawing.

The tricky thing is that often, table B has multiple buildings for the same address (including sheds etc.), and where this is the case, all of the buildings have the same value for HAS_SWIMMING_POOL_ADJACENT.

You need to combine all the buildings for each address before counting pools, to avoid multiple counting.

...

Graeme

990 post(s)
#21-Mar-18 03:11

Exactly. But getting the ADDRESS_DETAIL_PID into Table B (SA_BUILDING_POLYGON) via the query "Update Building w Address Query" is failing, so combining them isn't possible (hence the switch to 8, just to prove the workflow will yield an acceptable result which it does, and no doubt an equivalent in 9 will if I can get the address field into "B").

tjhb
10,094 post(s)
#21-Mar-18 03:13

It's very easy in 9, back in a minute.

Except that there are three possible values for HAS_SWIMMING_POOL_ADJACENT, 0, 1 or 2. 1 seems to mean yes, but what do 0 and 2 mean?

E.g. if 1 means yes, and 0 and 2 both mean no (but why?), then this will do the first step.

SELECT

  a.[ADDRESS_DETAIL_PID],

  GeomMergeAreas(b.[SHAPE]AS [Geom],

  MAX(

    CASE [b].[HAS_SWIMMING_POOL_ADJACENT]

      WHEN 0 THEN FALSE

      WHEN 1 THEN TRUE

      WHEN 2 THEN FALSE

    END 

    ) AS [pool]

FROM

  [SA_BUILDING_ADDRESS] AS a

  LEFT JOIN

  [SA_BUILDING_POLYGON] AS b

  ON a.[BUILDING_PID] = b.[BUILDING_PID]

GROUP BY

  a.[ADDRESS_DETAIL_PID]

;

You might want to turn this into a SELECT INTO query to create a new table, and maybe add a new drawing to it so you can make visual checks.

Then it's just a matter of doing the count.

Graeme

990 post(s)
#21-Mar-18 03:26

Correct assumption about the values - data's new to me too. 0 is unknown, 2 is no pool - info in "SWIMMIMG_POOL_ADJACENT_AUT".

I'll try your (not that easy SQL - thanks - still have Fehily open next to desk).

My approach is much more laborious, but I'd like to know why "Update Building w Address Query" is failing..

Graeme

990 post(s)
#21-Mar-18 03:39

Works a treat adding an INTO

SELECT

  a.[ADDRESS_DETAIL_PID],

  GeomMergeAreas(b.[SHAPE]AS [Geom],

  MAX(

    CASE [b].[HAS_SWIMMING_POOL_ADJACENT]

      WHEN 0 THEN FALSE

      WHEN 1 THEN TRUE

      WHEN 2 THEN FALSE

    END 

    ) AS [pool] Into [Tims Results Table]

FROM

  [SA_BUILDING_ADDRESS] AS a

  LEFT JOIN

  [SA_BUILDING_POLYGON] AS b

  ON a.[BUILDING_PID] = b.[BUILDING_PID]

GROUP BY

  a.[ADDRESS_DETAIL_PID]

;

but why is the resulting table read only, meaning objects cannot be cursor selected? Probably obvious..

Attachments:
Into result table read only.PNG

tjhb
10,094 post(s)
#21-Mar-18 04:29

but why is the resulting table read only, meaning objects cannot be cursor selected? Probably obvious..

It's not read-only; but as you say, rows can't be selected, because there is no BTREE index.

There is a BTREENULL index, on [ADDRESS_DETAIL_PID]. That index was added automatically. Why was it not a BTREE? Because in the source table, there is no BTREE index on the source field [ADDRESS_DETAIL_PID], or any other guarantee that the field would contain no NULL value(s). It had to assume that NULLs were posssible. (If there were, there would be one group with NULL in that field.)

It did know in advance that there would be no duplicate values in [ADDRESS_DETAIL_PID], because we grouped by that field. Otherwise it have had to make a BTREEDUPNULL instead.

Too much detail probably. Anyway, two easy ways to fix it:

If you happen to know that values in some field are unique, you can add a BTREE index on that field. Here, values in [ADDRESS_DETAIL_PID] are unique, so that will do fine.

ALTER TABLE [Tims result Table] (

  ADD INDEX [ADDRESS_DETAIL_PID_x 2] BTREE ([ADDRESS_DETAIL_PID])

  );

(You could get rid of the BTREENULL on the same field if you wanted to. It's no extra use now.)

Another way, if you don't know that values in some existing field are unique, is to tell the engine to add a [mfd_id] field, and add a BTREE index on that. Values added to this special field are made (and kept) unique automatically.

ALTER TABLE [Tims result Table] (

  ADD [mfd_id] int64,

  ADD INDEX [mfd_id_x] BTREE ([mfd_id])

  );

[Added.] A third way. Instead of using SELECT INTO then ALTER TABLE on the result, you can use CREATE TABLE first (adding either mfd_id or a field you know will contain unique values, with a BTREE index on that field), then using INSERT INTO to fill it.

Graeme

990 post(s)
#21-Mar-18 05:01

More really helpful explanations and suggestions Tim. I hope I'm not alone in benefiting from your patient expertise here. It's sinking in much better addressing a real data problem!

Dimitri


7,413 post(s)
#21-Mar-18 05:44

Tim has the generosity of a saint, I agree.

The need to have an index for a table to be editable (or selectable) is covered in the basic topics that, understandably, experienced GIS operators often skip. But they can still be useful. I encourage everyone, even experts, to consider a review of topics like Tables and Editing Tables, in case some of the other tips they provide may be useful.

tjhb
10,094 post(s)
#21-Mar-18 07:14

I think it must be while since I last read the Tables topic right through. Or I had just not read so attentively before. It flows really well, a pleasure to read and succinct, very impressive for something so detailed and covering so much ground. Definitely repays re-reading.

tjhb
10,094 post(s)
#21-Mar-18 03:48

I'd like to know why "Update Building w Address Query" is failing..

Good question.

Because table [SA_BUILDING_ADDRESS] contains both a field named [BUILDING_PID] and a field named [Building_PID_Copy].

So the test WHERE [Building_PID_Copy] = [BUILDING_PID] just tests those two fields within that table, and the query-expression is not corrleated with table [SA_BUILDING_POLYGON] at all.

Why that means the result is NULL I'm not so sure (though perhaps it makes intuitive sense--it's basically saying "unknown").

If you really, really want to do it this overcomplicated way, then you could (for example) delete the field named [BUILDING_PID] from table [SA_BUILDING_ADDRESS]. You would also have to remove the index [GDB_159_BUILDING_PID] which relies on that field.

Then the name [BUILDING_PID] in the WHERE condition must refer to table [SA_BUILDING_POLYGON], and the correlation will make sense.

That does work, though it is pretty slow (because there is no index on field [Building_PID_Copy]).


NB doing it that way, I notice that some buildings in [SA_BUILDING_POLYGON] are not matched by any address in [SA_BUILDING_ADDRESS]. That may be a problem for the simpler approach as well. Might need to think about that.

tjhb
10,094 post(s)
#21-Mar-18 06:19

Having worked through the wrinkles in this thread, I like Adam's approach a lot more than I thought. It's not overcomplicated. I just didn't understand it well enough until working through something tricky (same as Graeme).

Adam really packed a lot into his original explanation (here).

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.