Subscribe to this thread
Home - General / All posts - Can I alter a point's x or y via sql in M9?
bdg34 post(s)
#01-Sep-19 13:24

I am a past user of M8, for about a decade, that ended 2 yeas ago. I was just getting in to M9 then. I am picking it up again.

I am dealing with some US Census data for Block Groups and Congressional Districts. The data is not topologically aligned. My thought was to use the centroid of the Block Group and sum population by race to the Congressional Districts, while investigating Gerrymandering.

In the attached image the cyan dots are the centroids of the orange Block Groups. The green area is a Congressional District. Some Block Groups, in coastal areas, have a centroid that falls in the ocean. I have figured out how to clip the intersection of Block Group and Congressional District, and calculate the new centroid location for the the Block Group to sum its data into the Congressional district.

I am stymied about how to actually alter the existing point's X and Y values. I see functions to get the X2 value and parse it to constituent values but not to set new values. Is it possible to move a geom or do I need to create a new geom with the new x & y with the other attributes of the the old geom, and then delete the old?

Attachments:
BG-CD_issue.PNG

Dimitri


5,519 post(s)
#01-Sep-19 18:50

If it is an existing point (and not a computed field which on the fly computes a centroid for an area) use the Record pane Coordinate tab.

Editing Drawings

Record Pane

Example: Edit Attributes and Move a Point

I strongly urge you to read the user manual topics in the Getting Started chapter and the Basics chapter. Read all of them, and work through the examples in the Examples chapter and sub chapter, at least the introductory examples and the main ones for tables, drawings, etc, and those that look like they bear on a particular task that otherwise might not be clear.

bdg34 post(s)
#01-Sep-19 22:31

The point of asking if I could do it in SQL is because I have 929 points that I don't want to move manually. I have seen many of your YouTube demos, including moving a point to a pile of rocks and the recent Styles update. I found the folders features in the newest update most inviting. I am acquainted with the basic functionality.

I figured it out. Doh, it's incredibly simple!

update

(

select ofs.[Geom] as oldGeom, newC.[Geom] as newGeom, ofs.[GEOID_Data]

from [AnotherDwg 2 Table Center, Inner] as newC

join [offShoreCentroids Table] as ofs on newc.[geoid_data] = ofs.[GEOID_Data]

)

set oldGeom = newGeom

tjhb

8,846 post(s)
#02-Sep-19 00:04

That won't quite work. Back in a minute.

tjhb

8,846 post(s)
#02-Sep-19 02:07

I don't think your query will have produced a result, or even run, in Manifold 9.

You "should" have seen the error message "Schema should contain at least one unique index".

Please let me know if that is not the case.

There are two reasons why the query cannot work in 9, AFAIK.

(1)

For a table to be updated, the SQL engine must be able to tell one record from another. It can only do this if the table has (as the error message says) contains a unique index on at least one field. That means a BTREE index.

Most often, for built-in tables, there is already a BTREE index on the special field mfd_id. In some cases that may not be true, depending on the source of the data, but there may be a BTREE index on some other field or on a combination of fields. That is equally good.

If there is no BTREE index, then we can neither UPDATE the table using SQL, nor even select any records in the table with the mouse. That is for the same reason: the SQL engine (which the table user interface also uses) has no means of telling one record from another.

(This is new, in the sense that Manifold 8 handles all this automatically. We didn't even need to know about it. Now we do.)

For an UPDATE query to work in 9, the target table must include not only the target field(s) and some source field(s) or source expression(s), but also some field on which a BTREE index exists.

In simple cases, that is often as easy as including an existing mfd_id field in the list of fields.

But this is not a simple case, because of the join.

(2)

Take a look at your target table. To do this, highlight just the query text between the outer (...),

select ofs.[Geom] as oldGeom, newC.[Geom] as newGeom, ofs.[GEOID_Data]

from [AnotherDwg 2 Table Center, Inner] as newC

join [offShoreCentroids Table] as ofs on newc.[geoid_data] = ofs.[GEOID_Data]

and press Alt-Enter to execute just that portion of code.

Now press Ctrl-E to bring up the Schema for the resulting table.

(That's two great features, by the way. You may know about them already.)

What indexes do you see? (Indexes are at the bottom, after the list of fields.)

I would expect that you see RTREE indexes on [oldGeom] and [newGeom], and perhaps a BTREEDUP index on [GEOID_Data]. But crucially, no BTREE index at all. Is that right?

If there is no BTREE index, then the target table (created by the SELECT ... FROM ... [INNER] JOIN ...) cannot be updated.

This can be fixed, but first let's check whether that is indeed what you are seeing and if you have any questions so far.

Tim

bdg34 post(s)
#02-Sep-19 03:26

Thanks Tim. I did initially experience the cited error. The forum helped me find the answer to that. After I added a Btree index on the [GEOID_Data] in both tables My points moved to where I wanted to see them.

I do see the Btree Geoid_data_x index I had manually added, along withrtree for the two geoms.

I've been using Alt Enter alot. Control-E is new to me or at least I don't remember from past experience. Dimitri's admonition that I read the basics, especially after he suggested to another to re-read due to the rapid changes, did allow me to learn or relearn how to export a query result.

Maybe this discussion will help some one when they see the errorthemselves

Dimitri


5,519 post(s)
#03-Sep-19 10:11

Ctrl-E is just the keyboard shortcut to launch the Schema dialog, as an alternative to Edit - Schema. It's not related in any way to Alt-Enter.

Dimitri


5,519 post(s)
#03-Sep-19 10:20

The point of asking if I could do it in SQL is because I have 929 points that I don't want to move manually.

Sorry, I didn't see you wanted to use SQL. That's easy to do, using SQL Functions.

Those are easy to learn by using the Edit Query button in the Transform pane, as illustrated in the SQL Example: Learning to Union Areas in SQL from Edit Query topic.

Open a drawing, pick a template in the Transform pane that is similar to what you want to do, press the Edit Query button, and see what Manifold writes as SQL. If you want to shift points, choose the Shift template, use values for X and Y, and you'll get SQL like...

UPDATE (

  SELECT [mfd_id],

    [Geom],

    GeomScaleShift([Geom], VectorMakeX2(1, 1), VectorMakeX2(5000, 1000)) AS [n_Geom]

  FROM [MyPoints]

  THREADS SystemCpuCount()

SET [Geom] = [n_Geom];

... My drawing in the above is called MyPoints and I've shifted the points by 5000 m in X and 1000 m in Y.

By the way, if you want to shift all 929 points the same amount, you can just use the Shift Transform.

If you want to learn how to update points to new values, use the Compose Point template pointed at the Geom, to get something like...

UPDATE (

  SELECT [mfd_id],

    [Geom],

    GeomMakePoint(VectorMakeX2(33, 55)) AS [n_Geom]

  FROM [MyPoints]

  THREADS SystemCpuCount()

SET [Geom] = [n_Geom];

I just picked hard-wired X and Y values of 33 and 55 in the above for a simple example. If you want more complex, use an expression.

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