Manifold Community Site: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

jenerdba — Fri, 06 Jun 2008 08:06:12 GMT

I've ran into an issue while testing SQL Server 2008 with Manifold.

It seems that if I import geometry data based on a query into Manifold, the geometry column becomes a binary column.

Here are the steps I took:

1 - Open the Database Console (menu Tools | Database Console)

2 - Connect to a SQL Server 2008 data source

3 - Type a query in the bottom textbox: SELECT * FROM any table that has a geometry field.

4 - Click on the Import Query icon and close the Database console.

Now if you double click on the imported query in the project pane, you will see that the geometry column is now a binary column.

When I tried the same steps with Oracle11g, the geometry column was imported as a geometry.

Is this a bug?

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

csb — Fri, 06 Jun 2008 10:02:53 GMT

I think this is expected. I do not have Oracle so can't comment on that, but I believe this is is the way it has always worked. I have 8.0.6.0 (64bit). The way I import data from SQL 2008 is thru Import-Drawing-Datasource. Unless I just need a quick bit from the database console then I can import the query (like you do above) and then turn the resultant table (the one with the binary column) into a drawing via import-this project- tabels with geometry column.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

jenerdba — Fri, 06 Jun 2008 10:09:42 GMT

If I do import-this project-tables with geometry picking that query, the result drawing is totally wrong. I get only a little circle. Definitely a problem.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

csb — Fri, 06 Jun 2008 10:46:20 GMT

You are right! I just tested it and it does not work. I thought I had done this very thing a few months ago. Sorry. Import drawing does work for me...

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

jenerdba — Fri, 06 Jun 2008 10:54:39 GMT

Yes, import drawing always work.

It could well be a problem with the SQL Server 2008 client software, after all it is still a CTP.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

adamw — Sat, 07 Jun 2008 04:21:48 GMT

Importing or linking a drawing or a table with geometry values from SQL Server 2008 should always work no matter what. Importing or linking an explicit server-side query (like the one you specify in Database Console) should never work.

There currently are no means to retrieve geometry values (ok, spatial data, since this applies to both GEOMETRY and GEOGRAPHY types) from SQL Server 2008 without going through some sort of a conversion process. Importing or linking a drawing or a table lets Manifold write its own queries which do the conversion. Importing or linking an explicit query forces Manifold to use the query exactly as it is and so unless the query does its own conversion there is no way to get geometry values from the resulting records - and in many cases there is no way to get any records at all because the connection layer (OLE DB) can not deal with geometry values as well.

The situation with Oracle is different in that Oracle provides means to retrieve geometry values via OCI. Because of that importing or linking an explicit query that returns geometry values will still work.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

Kenneth — Thu, 21 Aug 2008 15:47:06 GMT

ADAMW,

BUT WE DO NEED THIS FOR CREATING AOI (By Query) LINKING TO IMS MAP.

Since current Version of IMS doesn't Support Dynamic AOI for IMS map loading, we need to create query and then link drawing from THIS PROJECT->TABLE WITH GEOMETRY COLLUMN. The query can be a dynamic AOI by replacing the select *. while the IMS is being browsing and searching.

Geometry columns of Oracle and postGIS are persisted in query resultant of Manifold and this function can be used. But geometry column of SQL Server is TURNED TO BINARY and linking to TABLE WITH GEOMETRY COLLUMNS can not be used for this purpose.

I have been searching for solution for this problem but I am really sad to read your message from here. Based on your posts, i thought you were from Manifold. This is the end of my searching for the solutions?

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

mapasPT1 — Fri, 22 Aug 2008 01:25:14 GMT

Hello

To import geometry data based on a query resulting in a View from MySQL into Manifold I do this:

1. File - Link - Drawing

2. In the data source box confirm the linkage to the database, in your case, to SQL Server

3. In the link drawing box, in TYPE select the TABLE WITH GEOMETRY COLUMN

4. after, in SOURCE select the VIEW that you have created

5. In GEOMETRY TYPE select the correct one

6. In VERSION select the correct column (usually the VERSION or the OID)

7. Click ok

8. Open the drawing and confirm the projection and ... "voilá"

This is the methodology that I use to open geometry data based on a query into Manifold in a relation with MySQL. I can use the Database console to open correctly original drawings but to open geometry data based on a query in MySQL that results in a view, I have to use that methodology.

I hope this helps in your case.

Regards

Joaquim

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

mapasPT1 — Fri, 22 Aug 2008 09:59:30 GMT

To complet my last message:

I have to do my queries first, inside the MySQL and create a view with the result and link it inside the Manifold like I described, because, in the way you - jenerbda - want, by using a query in the Database Console, I received a error message. I think it's because with MySQL I have to connect to the databases in a OLE DB Data Source way.

Regards

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

Kenneth — Fri, 22 Aug 2008 11:57:21 GMT

I now solved this problem by restoring the drawing table to SQL Server with Geometry in Manifold format. The steps are:

1. Import or Link "Drawing1" into Manifold.

2. Create a Query "Query1" with "Select * from [Drawing1 table]"

3. Run Query1, the resultant will include a column "Geom(I)"

4. Export the Query Result as Table to SQL Server. This table will have a column "GeomI"

5. Create Linked Query "Query2" from the Table in SQL Server (Step 4).

6. Link "Drawing2" from "Query2" (Table with Geometry Column, Geometry "GeomI")

7. Click the Drawing2, Drawing will show as normally.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

Kenneth — Fri, 22 Aug 2008 14:38:56 GMT

in Step 4, Export to Access.MDB or SQL Server 2005.

then Import again to SQL Server 2008. (This is to avoid the GeomI in Manifold format being transfered into SQL Server2008 format).

You can also add the GeomI column to your orignal Drawing in SQL Server 2008, then remove the generated table.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

Kenneth — Fri, 22 Aug 2008 15:56:27 GMT

another method:

When Exporting the table, Use OLEDB connection instead of SQL Server to export data to SQL Server 2008.

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

volker — Sun, 24 Aug 2008 12:21:14 GMT

you can do a query in the databse console that directly snap the geometry with this (works in SQLServer2005Express):

SELECT *, [Geometry] from Drawing WHERE column = xxx

maybe you need this before:

OPTIONS CoordSys("Drawing" AS COMPONENT)

RE: SQL 2008 and Manifold 8.0.7 - Not importing geometry from table

Kenneth — Tue, 26 Aug 2008 14:50:18 GMT

volker,

SQL 2005 have no spatial datatype. The [geomtry] is presented as image. There is no problem in SQL 2005.

In SQL 2008. Geomtry is present as GeometryType. The work around is to convert the GeomtryType to Image Type.