Subscribe to this thread
Home - General / All posts - M9 SQL to export to DBMS
danb


1,656 post(s)
#08-May-18 06:03

I am taking my first tentative steps undertaking ETL type work in M9 using SQL. I am pulling data from a couple of databases, performing some intersection and attribute calculation within M9 and then exporting the results to PostGRE SQL. I have found that I can simply use the following to export my intersection drawing to a PostGRES connection using something as simple as:

SELECT *

INTO [PostGRES Connection]::[ PC1_X_PARCELS]

FROM [PARCEL Overlay Topology, Intersect Drawing];

Which is great. However when I bring the exported drawing back into M9, the projection is marked in red as Pseudo Mercator under the Contents Pane (My default coordinate system for new components is New Zealand Transverse Mercator (NZTM)).

I can assign the correct projection (NZTM), but I am wondering if there is a correct SQL syntax from Manifold to export to DBMS such as PostGIS, Oracle etc which correctly populates the DBMS spatial data metadata tables, or should I be switching to the native SQL of the database to which I am exporting?

Any pointers to relevant documentation etc would be much appreciated.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

adamw


8,139 post(s)
#08-May-18 09:55

The SQL for copying coordinate systems is fairly simple:

--SQL9

ALTER TABLE [Target Table] (

  ADD PROPERTY 'FieldCoordSystem.Geom' ComponentCoordSystem([Source])

);

But.

Since your target drawing is in PostgreSQL, you probably want to set up an SRID so that PostgreSQL also understands what the coordinate system is, and for now that's best done using PostgreSQL:

--SQL9

 

-- put data into PostgreSQL, SRID 0

SELECT [Geom] INTO [PostgreSQL DB]::[exported] FROM [Points];

 

-- adjust coordinate system in geometry_columns, SRID 3857 = EPSG 3857

EXECUTE [[

  SELECT UpdateGeometrySRID('exported''Geom', 3857);

]] ON [PostgreSQL DB];

 

-- adjust coordinate system in the geoms, SRID 3857 = EPSG 3857

EXECUTE [[

  UPDATE "exported" SET "Geom" = ST_SetSRID("Geom", 3857);

]] ON [PostgreSQL DB];

The above has three steps, but you can run it as a whole.

After you run it, right-click the data source for PostgreSQL in the Project pane and select Refresh to pick up changes to the coordinate system made behind the back of the application (they were made by issuing direct commands to PostgreSQL, so 9 cannot see that these direct commands were about changing the coordinate system of a component).

We are planning to make some changes in this area in the near future, they should make it a little more cohesive.

danb


1,656 post(s)
#08-May-18 19:33

Thanks so much for this Adam. Much appreciated. I can see a lot of work for M9 doing this sort of back end heavy lift in the future.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

danb


1,656 post(s)
#09-May-18 08:44

I probably need to do some more reading, but is it possible to run more than one native SQL statement at a time within a single EXECUTE [[ ]] ON DataSource construct? I was trying to get the export working exporting to an Oracle connection where I wanted to run a COMMIT statement immediately after the Oracle SQL. Doing this however yielded an 'Invalid Character' Oracle error which turned out to be due to the ; at the end of the SQL statement. With the delimiter removed, I couldn't send both the SQL statement and the COMMIT within the same EXECUTE [[ ]].

While I am here I also wanted to ask about the speed of refresh of a database on M9 which seems to be much slower than for M8. We have a very slow Oracle connection. From M8 this takes ~6 seconds to refresh the connection in the database console. In M9 from the same machine it takes ~45 seconds to do the same.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

adamw


8,139 post(s)
#10-May-18 14:27

How much you can fit into an EXECUTE for a foreign data source is determined by the limitations of that data source. EXECUTE compiles the text first because it needs to know what the return table is going to be before running anything. PostgreSQL can generally only compile a single statement (mostly because multiple statements may depend on each other, it's up to the database to decide what to do about it and PostgreSQL decided to simply disallow multiple statements).

You should only use EXECUTE to do COMMIT for a transaction you explicitly started yourself in the same EXECUTE statement. You cannot do something with the database intermixing Manifold / database-native SQL and then try to commit it all via EXECUTE + COMMIT - even if this happens to work now in your specific scenario, it might easily fail later because we can absolutely alter the logic in the query engine to start / end transactions where there are currently none, breaking the chain of changes. Or vice versa, we can remove transactions where they currently exist.

adamw


8,139 post(s)
#10-May-18 14:41

Regarding the speed of refresh for Oracle being worse than in 8 - this might be related to 9 recognizing more data, but let's try to determine what specifically happens anyway:

Close and reopen the MAP file. Don't open the data source for Oracle just yet. Right-click the unopened data source and select Properties. Right-click the value for the Source property and select Edit. In the dialog, add the following option to JSON: "InternalLogHandshake"=true (separate from other options with a comma, eg: { "Source"="...", "InternalLogHandshake"=true } ). Click OK to accept the edit, click OK to accept changes to properties. Now open the data source. The data source should be reporting various times into the log window. Send the contents of the log window to tech support, referencing this thread and saying that you want to report a potential bug (also see instructions for submitting bug reports).

danb


1,656 post(s)
#11-May-18 07:13

Thanks for your continued assistance Adam. I added "InternalLogHandshake"=true to the json string as suggested but nothing was written to the log other than:

2018-05-11 18:11:11 -- Load library: c:\oracle64\product\11.2.0\client_1\bin\oci.dll

Cool feature to have for helping diagnose problems BTW


Landsystems Ltd ... Know your land | www.landsystems.co.nz

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