This topic discusses the storage of geometry within tables, whereby binary data representing the shapes and locations of objects is stored within the table. That is a more advanced usage than the relatively simple case of geocoded tables, where some columns give lists of X/Y or latitude and longitude locations. For the more simple case of geocoded tables, see the Linked Drawings from Geocoded Tables topic.
Let's begin with a refresher on nomenclature: the objects in a drawing are the areas, lines and points that make up that drawing. A drawing's table has a record, a horizontal row, for each object. If there are any data attributes attached to the drawing's objects, those attributes will appear as columns in the table. The information that defines the locations of points and the locations and shapes of lines and areas is called the geometry of the drawing. The metric of a specific object is the geometric data that defines that object.
When working with drawings in Manifold we quickly become used to the idea that there are two parts to a drawing: the drawing component itself and the drawing's table.
When we pop open the drawing component in a drawing window it paints the objects in the drawing for us.
Because GIS as done interactively is primarily a visual activity, drawings and other visual displays take the lead role in Manifold's user interface.
When we pop open the drawing's table in a table window we see any data attributes in a classic row and column tabular presentation. Data attributes in tables are normally the sort of information that is easiest to comprehend and edit within a classic, text, row and column tabular user interface.
The way Manifold is taught to beginners together with the routine use of drawings and the tables of drawings conveys the idea that the geometry of objects is stored within the drawing and the data attributes are stored in the drawing's table, as if they were two different storage locations. But the truth is deeper, more general and more powerful than that.
The truth of the matter is that inside Manifold all information for drawings, both geometry and attributes, is stored within tables. It is just a question of how different types of information are best displayed and used. Since geometry is best understood using a visual interface, by default the geometry in a table is shown using a drawing. But the geometry is still in the table.
We can see the geometry data within every drawing's table by turning on the Geom (I) implicit field. The Geom (I) column reveals the geometry column of a table, wherein the metric of each object is stored using Manifold's own Geom data type, a form of binary data that encodes an object's metric.
It is impractical in a text, tabular presentation to convey a visual impression of geometry so instead when the Geom (I) column is turned on a table will report some summary information about the metric for that object: the type of geometry data, the type of object (area, line or point), the number of branches and the number of coordinates.
Since geometry is best comprehended visually, Manifold uses a visual, drawing user interface to present geometry. But the geometry that is displayed or edited in a drawing window is still ultimately stored within the table.
When we edit the shape of an object in a drawing, we are simply taking advantage of the drawing as a handy, visual user interface to edit the contents of the Geom (I) field for that object in the table. For example, we can select an object for editing in a drawing.
The object's row is also selected in the table. We can use that to follow along in this example.
Deleting a coordinate in the object changes both the geometry and the visual appearance of that object.
We can see by the coords: 6 value changing to coords: 5 that the geometry in the table has also been changed (there is now one less coordinate in the metric). The drawing is simply a visual interface to geometry stored in the table.
When we create a drawing the two part user interface system for visualizing drawings and drawing tables is automatically put in play by Manifold.
Manifold automatically creates a drawing component with the drawing's table indented under it in the project hierarchy to indicate the association between the two. But this is just a choice of user interface to present two icons, a drawing icon and a table icon, upon which users can click depending on whether they want to see the data as a visual drawing or as a table. A different choice in user interface design might have simply used one icon and then given users the choice to click it open as a drawing or to click it open as a table.
In fact, within Manifold we can create tables that contain geometry which do not automatically have a drawing window user interface associated with them.
In the above example, we can copy the Drawing Table component and paste it as a new table called Drawing Table 2. This is now simply a table not associated with any drawing.
After simplifying the illustration by hiding undesired columns and renaming the geometry column (to emphasize for the purposes of this illustration that it is not an intrinsic column anymore), we get the display seen above. This is now simply a table that contains a geometry column. We can link a drawing to it, write a query that grabs a subset of data from it, export it to an external DBMS or do many other things with it.
Exploiting Geometry in Tables
Once we understand that geometry is stored in tables we can exploit that understanding in many useful ways. We can:
§ Store tables within external databases, including the geometry of objects.
§ Translate geometry within tables from Manifold format to other formats for geometry.
§ Export tables containing geometry to external databases, in effect a way of storing drawings within external database systems.
§ Import tables containing geometry from external databases, in effect a way of importing drawings stored in databases.
§ Write queries that dynamically select desired geometry from tables.
§ Link drawings from queries that select geometry.
§ Link drawings from tables in external databases, in effect a way of letting a DBMS system maintain a drawing that potentially thousands of simultaneous Manifold users might be editing simultaneously.
If an external DBMS can store data in binary form (as can all of the big-name DBMS packages), we can store drawings within external DBMS servers by simply exporting tables containing geometry to those servers.
Note that the realization that the geometry of a drawing is encapsulated within the drawing's table as a Geom (I) column is different than the simple notion of a geocoded table. A geocoded table is a way of maintaining a list of point locations in a table. It is simply a table that for each record contains latitude and longitude columns specifying where that record is located.
Geocoded tables are useful mainly for points and cannot store areas at all and lines to only a limited degree. Geocoded tables are a low performance, limited solution. Drawings linked from geocoded tables are read-only and cannot be edited. The main reason Manifold provides a simplified way of linking drawings to geocoded tables is that lists of latitude / longitude locations in tables are extremely common and it is likewise a common desire to see such locations displayed in a map. So Manifold provides a simple way of doing that.
In contrast, tables that contain a geometry column have the ability to fully specify drawing geometry with all the characteristics sophisticated GIS usage requires of objects: that they can be areas, lines or points; that they can be multi-branched for topological sophistication, that they can exist within a specific coordinate system. To distinguish tables containing a geometry column from simple geocoded tables we often refer to them as spatial tables within Manifold documentation.
When stored within a fast DBMS spatial tables can be a very high performance solution that scales well for use by the very largest enterprise applications. Spatial tables provide tremendous flexibility and power. Drawings linked from spatial tables are editable, assuming the connection technology used is read/write and the user also has write permissions for the DBMS table used.
The usual way of storing spatial tables is within a spatial DBMS. Manifold makes it easy to store geometry data within spatial DBMS.
Using Geometry Data in Tables
Why store geometry within spatial tables? Doing so allows use of linked drawings that are dynamically created from tables or queries containing geometric data. Because of the great flexibility of SQL the ability to create linked drawings from tables or queries makes it possible to create drawings on demand to suite a wide variety of purposes that cannot be fulfilled by static drawings.
Besides the obvious benefit of creating linked drawings on the fly as desired, there are many practical gains from storing geometry data in tables. To name just a few examples:
§ We can store metric data in geometry columns in order to centralize all spatial data within a database, perhaps a central DBMS using SQL Server.
§ We can store metric data in geometry columns to have more than one metric for a drawing object. For example, we could associate one object with another object without using any intermediate columns such as IDs, thus avoiding any risks of broken associations. Or perhaps we might want to store different versions of an object's metric for different projections or for different users.
§ We can use geometry values to store temporary results of geometric computations within queries.
§ We can edit the Geom (I) intrinsic field in tables associated with drawings to edit the objects in the drawing.
§ We can transform geometry data into different types to enable interoperability with different GIS systems.
§ We can transform geometry data into native geometry types provided by spatial DBMS products like Oracle Spatial, to take advantage of server-side facilities provided by such products.
§ Drawings linked from geometry in tables can be edited.
§ If we use Enterprise Edition, drawings linked from geometry in tables can be edited by multiple users at the same time.
§ If we use Enterprise Edition and a spatial index within a spatial DBMS, drawings linked from a spatial table can be restricted to a given area of interest. This enables desktop Manifold installations to be used to work with a manageable subset of what could be an immensely large data set.
Geometry Types in Tables
Tables can contain geometric data columns that may use the following data types:
§ Oracle SDO_GEOMETRY data, within Oracle databases that support this data type. This data type occurs only within Oracle tables and is dynamically mapped on the fly back and forth between Manifold's own Geometry type within tables in Manifold projects during OCI connections..
§ IBM ST_GEOMETRY data, within IBM DB2 databases that support this data type. This data type occurs only within IBM tables and is dynamically mapped on the fly back and forth between Manifold's own Geometry type within tables in Manifold projects during native DB2 interface connections.
§ The Geometry type (also called Geom) is specific to Manifold System and contains the object type, the object metric and the coordinate system. Geom data can be stored within Manifold projects or saved into almost any DBMS system that supports binary "blob" storage, such as Access or SQL Server or MySQL. Exchanging data with Oracle data sources via the Oracle Call Interface (OCI) automatically maps Manifold Geometry values into Oracle SDO_GEOMETRY values (collections of geometric entities are not supported). Exchanging data with DB2 data sources via the native DB2 interface automatically maps Geometry values into DB2's ST_GEOMETRY values.
§ The Geometry (SDE) type (also called GeomSDE) is a binary data type used by ESRI products to store data in external databases. The values of this data type contain a compressed stream of integer coordinate values. The correct encoding of coordinate values relies on data stored in external metadata tables. The object type and coordinate system are also stored externally. Without external metadata tables, areas are indistinguishable from lines.
§ The Geometry (SHP) type (also called GeomSHP) is another binary data type used by ESRI products to store data in external databases, most often in "personal geodatabases". The values of this data type contain coordinate values and the object type. Coordinate system is stored externally.
§ The Geometry (WKB) type (also called GeomWKB) is the "well-known binary" or WKB data type defined by OGC (Open Geospatial Consortium) and used by OGC-compatible products. This is often abbreviated to GeomWKB in casual conversation. The values of this data type contain coordinate values and the object type. Coordinate system is stored externally.
Geometry, Geometry (SDE), Geometry (SHP) and Geometry(WKB) data types are referred to as Geom, GeomSDE, GeomSHP and GeomWKB as SQL data types and in coding. Therefore, these shorter names for these geometry data types are the usual way experienced Manifold users refer to these data types. For example, a Manifold user might say "I need to convert that Geom to a GeomSDE here." In addition, the GeomWKB type is often abbreviated even more to simply WKB. This documentation will use either the longer forms or, more frequently, the shorter names for these data types.
Geom, GeomSDE, GeomSHP and GeomWKB data types can be implemented within any DBMS that can store binary data. In addition to tables within Manifold System, many modern database systems, such as SQL Server, Oracle and Access can store data in this form. Manifold can work with external tables in an external DBMS provider and save in Geom, GeomSDE, GeomSHP or GeomWKB form.
Geom and GeomWKB can be used within SQL Server using the Manifold Spatial Extender for SQL Server. This is by far the highest performance for storing drawings with a spatial index in SQL Server, especially when Geom is used. See the Spatial DBMS topic for information on the spatial extender.
GeomSHP and GeomSDE are somewhat similar approaches with differences in binary data organization. GeomSHP stores coordinates as double-precision floating point values. GeomSDE stores coordinates as integer values which are compressed using a simple run-length encoding scheme. The coordinate values must be scaled and shifted using the information stored in metadata tables.
Because GeomSDE depends upon metadata tables for meaning, some assumptions must be made when converting other geometry types into GeomSDE or when importing or linking a drawing from a table using GeomSDE. In both cases the integer coordinate values within GeomSDE values are assumed to use offsets of 0 and a scale of 1.
The Geom data type captures coordinate system information, but the GeomSDE, GeomSHP and GeomWKB types don't. When used with projection-aware algorithms, values of the GeomSDE, GeomSHP and GeomWKB types are assumed to be in Latitude / Longitude. Working with values of these types in a projection other than Latitude / Longitude requires assigning these values the correct projection, for example, by converting to the Geom type and then using the AssignCoordSys query function.
Oracle SDO_GEOMETRY is available only within Oracle DBMS products. Manifold Geom data and Oracle SDO_GEOMETRY data are seamlessly mapped into each other during exchange whenever OCI is used to connect to Oracle databases. To force use of generic, binary-style storage instead of the native Oracle SDO_GEOMETRY storage within Oracle, connect to the Oracle data source using OLE DB or ODBC or (read only) ADO .NET.
Similarly, IBM ST_GEOMETRY is available only within IBM DBMS products. Manifold Geom data and IBM ST_GEOMETRY data are seamlessly mapped into each other during exchange whenever a native DB2 interface is used to connect to IBM DB2 databases. To force use of generic, binary-style storage instead of the native IBM ST_GEOMETRY storage within DB2, connect to the DB2 data source using OLE DB or ODBC or (read only) ADO .NET.
A simple rule for choosing from the above types is to always use a spatial DBMS vendor's native type. Use SDO_GEOMETRY with Oracle and ST_GEOMETRY with DB2, for example. Otherwise, use Manifold's Geom type. In rare cases where interoperability with open source foolishness is more important than performance or reliability, we might be forced into using WKB. It almost never makes sense to use the ESRI types unless for the very limited case of interoperability with data held hostage in ESRI form.
Choosing a Geometry Data Type for Interoperability
One reason to store geometry in tables within database systems is to allow other applications to use such data. If we don't care about legacy applications and simply want our spatial DBMS to run as fast as possible while enabling multi-user concurrent editing we can simply use Manifold Geom type and either SQL Server with the Manifold spatial extender or Oracle (with Geom being automatically mapped into Oracle's SDO_GEOMETRY for us) or IBM DB2 (with Geom being automatically mapped into IBM's ST_GEOMETRY). If we want interoperability with other applications we have to decide what DBMS to use and also what geometry type to use.
When database tables containing columns of the above geometry types are saved from Manifold to an external DBMS provider, Geom values will be mapped to SDO_GEOMETRY if native Oracle interfaces are used or ST_GEOMETRY if native DB2 interfaces are used. In other cases, the data in such columns will appear as generic binary data
If the geometry data is in GeomWKB form, any application that understands GeomWKB will be able to use it. Although there are some, mostly academic, applications that can read or write GeomWKB, very few commercial applications have been deployed that use it (for reasons set forth below).
If the data is in Oracle SDO_GEOMETRY data then any application that understands SDO_GEOMETRY will be able to use it. Using Manifold + Oracle therefore will usually be the default choice for many people since more commercial applications can directly read or write SDO_GEOMETRY geospatial data than any other format.
IBM DB2 spatial users will want to use ST_GEOMETRY to allow interchange with any application that understands ST_GEOMETRY. DB2 also has a strong following, albeit with fewer applications than Oracle at this writing that can exchange spatial data using IBM's ST_GEOMETRY data type.
At first glance GeomSDE or GeomSHP might appear to be a way to allow easy interoperability with ESRI products, but what are commonly understood as ESRI "geodatabase," "ArcSDE" or "personal SDE" formats are not at present well supported by GIS industry products (not even by all legacy ESRI products) nor has ESRI encouraged interchange by other vendors using these data types. They remain closed, largely undocumented formats that have not been widely adopted outside ESRI. They also suffer from dependencies upon external metadata which makes them harder to use than self-contained types. The primary use for these types in Manifold is to facilitate data exchange with existing data sources managed by ESRI products. Their use in other cases makes little sense.
Choosing Between Geom and GeomWKB Types
Suppose we are working with a DBMS like SQL Server or MySQL that requires us to choose between GeomWKB and Manifold's own Geom type. Given that other applications are emerging that also understand GeomWKB, why would Manifold have a Manifold Geom type for storing geometry in tables? Would it not be easier to always use GeomWKB?
The reason Manifold has its own Geom type in addition to GeomWKB is that GeomWKB is limited. It does not contain the coordinate system in use, nor does it provide the ability to embed a variety of useful geometric information such as location precision or other data that can be cached to improve performance.
A good plan, therefore, is to use GeomWKB if one must have interoperability with existing applications that support it and use Geom for all other things. The storage impact for coordinate system and other data that are stored in Geom is not very large (typically less than 40 bytes and sometimes as little as 2 bytes per record) so using GeomWKB instead of Geom does not provide significant space savings.
On the other hand, using Geom lets us transfer tables with geometric data between machines without having to worry about coordinate systems. It also lets us have a table with objects that are in different coordinate systems, which will be automatically re-projected to the coordinate system of the linked drawing when it fetches data from the table.
Geom is also the native geometry type of Manifold itself. This has the utility of being always available in any drawing within Manifold without need of conversion to a generic GeomWKB type.
GeomWKB (WKB) and WKT
WKT is the "well-known text" format used by OpenGIS and similar standards. It is a human-readable text analog of GeomWKB. When viewing WKB data in tables, the data can be reported either in <geom wkb, type> condensed format (the default), which only shows the type, or in WKT format, which shows both the type and the coordinates. To switch between the formats, right-click the WKB column, and select Format.
We can also transform GeomWKB data into WKT with CStr or CAST to create a text column containing WKT:
SELECT CStr(CGeomWKB(Geom(ID))) FROM [Drawing];
Queries and Geometry
Queries are used to create and alter columns containing geometric data. The SQL name of the Geometry type is Geom. The SQL name of the Geometry (SDE) type is GeomSDE. The SQL name of the Geometry (SHP) type is GeomSHP. The SQL name of the Geometry (WKB) type is GeomWKB. Using queries we can create geometric data from existing objects or literal values, we can convert geometric data between various representations and we can transform geometric data within tables. We can also use queries to create subsets of geometric data or to combine data from different tables. See the Spatial Extensions topic for a list of SQL functions that operate on Geom, GeomSDE, GeomSHP or GeomWKB data.
Creating a Table with Geometry
Tables storing geometry data are created from drawings with queries using the Geom (I) intrinsic column or the Geom(ID) call (which takes the value of the ID column and extracts the Geom for that value using the Geom() function).
Creating a Table storing a Drawing's Geometry
1. Create or import the drawing into the project.
2. Write a query extracting the geometry data from the drawing.
3. Run the query.
4. Save the query results as a table.
A typical query that captures a drawing's geometry as the Geometry column type as well as all data fields in the drawing would be…
SELECT Geom(ID) AS [Geom], [Drawing].*
FROM [Drawing];
…where Drawing is the name of the drawing.
The query can then be used to create a table. To capture a drawing's geometry as the GeomWKB column type we first use Geom(ID) as above and then use the CGeomWKB(geom) function to convert the data to WKB form. These two functions may be nested, as in
SELECT CGeomWKB(Geom(ID)) AS [GeomWKB], [name of drawing].*
FROM [name of drawing];
To covert geometry in Geometry or GeomSHP form into GeomWKB form, use CGeomWKB. To covert geometry in Geometry or GeomWKB form into GeomSHP form, use CGeomSHP. For example, after creating a drawing containing a GeomWKB column using the query above we could create another drawing containing the same data within a Geometry column using
SELECT CGeom(GeomWKB) AS [Geom] from [name of geom drawing];
An alternate way of creating a table that stores a drawing's geometry is to use a Select Into query. This is used when creating a table in the same project. Create and run a query like the following:
SELECT Geom(ID) AS [Geom], [Drawing].* INTO [Table]
FROM [Drawing];
…where Drawing is the name of the drawing.
Example
In this advanced example we will create a table containing geometry from the Mexico_eg example drawing found on the Manifold CD. We will then use that table to create a linked drawing.
This is a very artificial example in that normally the use of spatial data is with a spatial DBMS. When Manifold exports a drawing to the spatial DBMS there is no need to explicitly create a geometry column within a drawing and to literally and explicitly export that geometry column. Manifold will simply export geometry to the spatial DBMS as needed when exporting a drawing. This example takes a different approach in that it exports geometry in a highly manual way, as if it were a binary field, without taking advantage of the automatic export of geometry for drawings to a spatial DBMS as is normally done.
However, for advanced users there is useful education to be had in explicitly creating geometry and then exporting it to a table. This exposes the inner workings of geometry and shows how it plays a role in defining objects in drawings.
We import the Mexico_eg file into our project to create a drawing and the drawing's table.
The Mexico_eg drawing shows the provinces of Mexico. It is in latitude / longitude projection.
If we open the drawing's table we see that it has various fields for each province.
We will use this drawing to create a table that captures both the drawing's geometry and all data fields in the drawing within the table.
To do so, we create a query with the above text and run the query.
The query captures all of the drawing's fields and includes a new geometry field. Manifold reports statistics for Geom type fields including the type of object, how many branches it contains and the number of coordinates that define it.
The query also captures the object ID field from the drawing because the query text we used simply selected all of the fields from the drawing. We don't need to capture this field since it will not necessary to capture the geometry of each object. We are including it simply because it is easier to write a query that selects all columns than it is to write a query that selects all of the columns instead of the ID column.
We can save this query to a database table. In this example we will save it to an Access .mdb file in a temporary folder. To do so, with the focus on the query results window we choose File - Export - Table.
In the Export dialog we choose MDB Files (*.mdb) in the Save as type box and provide a name for the file.
In the Export MDB File dialog we choose Access 2000 as the MDB Subtype and press OK. [Moments after pressing OK we realize that we could have unchecked the ID box to not export that field, but that's OK - we will drag it along even though we don't need it.]
At this point we have created an Access .mdb file called Mexico_eg Geometry in our tmp folder. The .mdb file contains one table that contains a record for each object in the Mexico drawing. Each record has a column for geometry data defining the area object and columns for the various other data attributes. In an Access .mdb file, the geometry data is stored as binary data. Manifold can interpret that geometry data to create the line, point or area objects within a linked drawing.
Let's now pretend that we've not just created this table, but rather that it was sent to us by a colleague. We know the table contains geometry data that can be used to create a Manifold drawing, so we will link it into our project and use it to create a linked drawing.
We begin by linking the table from the external .mdb file into our project. To do so, we choose File - Link - Table.
In the Link dialog we choose MDB Files (*.mdb) in the Files of type box and then we navigate to the .mdb file and double-click on it.
Manifold will pop up a subsidiary dialog asking what table we want to import, and we choose the only table in the .mdb file. The default name of the table in the file is "Query," which is confusing, so as soon as we link the table into our project we rename it to something more sensible, namely Mexico Geometry. (This step is not illustrated).
The result is that now we have a linked table in our project called Mexico Geometry.
If we open this table we see that it has all of the fields from the original drawing plus a field called Geom that contains binary data. Note that the field containing binary data could be called anything. It happens to be called Geom because that's what we named it in the query that created this data from the original drawing. That's a useful name to use for such columns so that right away we know they contain Geometry data if we encounter them in tables.
To create a linked drawing from this table we choose File - Link - Drawing and then in the Link dialog choose This Project () in the Data Source dialog.
In the Link Drawing dialog that follows we choose Table with geometry column in the Type box and the Mexico Geometry table in the Source box. For this example, we have left all of the column boxes in the Columns pane checked. This means that all of them will be imported and available as columns in the drawing's table. However, we don't need either the ID column or the Geom column in the drawing's table. The ID column gives the object IDs for the original drawing.
The column name to use for geometry has already been loaded with the name of the only binary column in the table, Geom, and the type of geometry column has been loaded with Geometry. We press OK.
This creates a new linked drawing, called Mexico Geometry Data in the project. (Importing or linking a drawing from a table or query in the same project will inherit the name of the drawing from the table or query.)
If we open the drawing we see it is just like the original Mexico_eg drawing. It has automatically opened in Latitude / Longitude projection. We can use this drawing to create labels, to create themes, as layers in maps and for just about any other purpose we use a drawing in Manifold.
Let's now do something interesting and create a query that selects only some of the records from our spatial table. We can then create another linked drawing from that query.
We create another query, called Query 2, and write the above text into it to select all records from the Mexico Geometry table for which the SQMI value is greater than 30000. This selects only those records representing larger provinces in Mexico.
To create a linked drawing from the query, we choose File - Link - Drawing and then choose This Project () in the Data Source dialog.
In the Link Drawing dialog we choose Table with geometry column as the Type and Query 2 as the Source to use. Since we don't need the ID field we uncheck it. Press OK.
The result is that a second linked drawing, called Query 2 Data, appears in our project.
If we open this drawing we see that it contains only the larger provinces in Mexico, those with square mile area greater than 30,000 square miles.
Let us now illustrate the dynamic power of linked drawings by editing the table from which our linked drawing is generated, which will result in changes to the drawing.
We open the Mexico Geometry table and select the record for Durango, a large province in the middle of Mexico.
We press Edit - Delete to delete the record. Even though this is a linked table brought into the project from an external .mdb file, we can delete records in the table because when we linked the table we did not link it in read-only mode. Deleting the Durango record in the linked table actually deletes it in the external .mdb file as well.
If we click on the Query 2 Data linked drawing and press View - Refresh Data to refresh the drawing, we compel the drawing to refresh itself from the query from which it is created. The query in turn refreshes itself from the linked table and the external .mdb file in which the (now altered) data ultimately resides.
We can see that the drawing no longer includes a Durango area. The red arrow indicates the empty space where Durango used to be. [Note for the very literal-minded: Manifold does not actually draw red arrows when objects are deleted - the red arrow was added to the illustration.]
If we like, we can open the Mexico Geometry table again and click Edit - Undo to restore the Durango record. This restores it in the external Access .mdb file as well.
If we click back onto the Query 2 Data linked drawing and press View - Refresh Data we see that Durango has been restored. Refreshing a linked drawing created from a table that resides in an external data source will refresh the table. Refreshing a drawing linked from a query will recompute the query.
One last thing: if we like, we can use Edit - Projection to re-project the Query 2 Data drawing into Orthographic projection, using the Suggest button to choose sensible parameter.
Even though the geometric data in the originating table is in Latitude / Longitude projection, Manifold will re-project on the fly to create the drawing in Orthographic as we have specified.
Example Scenarios
Linked drawings created from tables or queries have great flexibility. In addition to the above example, consider these scenarios:
Scenario 1 - We have a drawing of states and want to create a separate drawing of states for each region. We want to keep all geometric data in the same place so all geometric transforms run in the same context, for example, with the same location precision.
We create a query for each region such as…
SELECT Geom([ID]), [Region]
FROM [States] WHERE [Region] = "East";
… and we link a linked drawing from each query. We can then modify the source drawing as desired and thereafter launch a script to update regional drawings so they use the latest data.
Scenario 2 - We have several different drawings of roads that cover different areas in our area of interest. We want to combine these drawings into a "master" drawing so we can operate upon all roads as a whole. We could create a master drawing using copy and paste, but the drawings are regularly updated and having to repeat the copy and paste process to re-create the master drawing each time a drawing is updated is not very convenient.
We create a query that joins the drawings together in the following form…
SELECT Geom([ID])
FROM [West]) UNION (SELECT Geom([ID]) FROM [East]);
…and we link a drawing from this query. When one of the source drawings is updated, we can update the linked drawing using View - Refresh Data.
Scenario 3 - We have a drawing of WiFi access points with an operational radius for each access point stored in the drawing's table. We want to display all WiFi access points in the given area as circles of appropriate size.
We create a query such as…
SELECT Buffer(Geom([ID]), [Radius], "mi")
FROM [Access Points]
WHERE EXISTS (SELECT * FROM [Areas]
WHERE Contains([Areas].[ID], [Access Points].[ID]));
… and we link a drawing from the query. Note the use of the buffer operator to create circular objects. In this case instead of formatting a point to be larger or smaller we actually create a circular object that is larger or smaller for display.
Scenario 4 - We have an ESRI geodatabase file in .mdb format. We can create a drawing from this geodatabase by choosing File - Import - Drawing and choosing .mdb in the Files of type box to open the .mdb file.
In the Import MDB File dialog we choose Geometry (SHP) in the Type box and press OK. The result is an imported drawing. We could have also used File - Link - Drawing to link a drawing from the geodatabase if preferred.
Note: This too is an artificial example in that if we want to work with ESRI personal geodatabases we can simply import or link to the geodatabase using Database Console. Manifold will automatically detect personal geodatabases and can import or link to them.
Display of Geometry Data within Tables
Tables will display the contents of specialized data types as a descriptive word within angle brackets <>. Tables display binary data as <binary>, coordinate system data as <coordinate system> and geometry data as <geometry>.
Right clicking on a geometry column head and choosing Format allows us to change the format of how the column is reported. For example, a coordinate system column can be switched between the default format and XML format, which will output XML formatted coordinate system information into each cell. This format is mainly used for debugging or when one wishes to capture a text / XML reporting of coordinate system structure.
For example, if we want to capture the coordinate system from a drawing of Mexico we could write a query called Q as follows:
SELECT CCoordSys(Geom(ID)) INTO X FROM [Mexico Drawing];
Running the above query creates a table called X that has one column containing the coordinate system data.
If we opened table X we could see that by default it reports the coordinate geometry data as <coordinate system>.
We can right click on the column heading, choose Format and change the display style from <default> to XML.
The result is that each cell reports the coordinate system used using a (lengthy) XML phrase. If we wanted to capture the XML we could click into a cell to edit it, copy the XML and then paste it wherever we wanted.
Importing or Linking from Spatial DBMS / Exporting to Spatial DBMS
This topic has discussed the internal mechanics of geometry stored in tables. When drawings are stored in a spatial DBMS there is no need to work directly with geometry columns. Simply use the Database Console to connect to the desired spatial DBMS and then link or import drawings as desired. Manifold will automatically transform the geometry type within the spatial DBMS into Manifold types, either automatically translating the spatial DBMS package's native geometry type into Manifold Geom or if a Manifold geometry type is already stored into the spatial DBMS using that type to create a drawing directly.
Likewise, when exporting a drawing to a spatial DBMS, if Manifold Enterprise Edition or greater edition is used Manifold will automatically export the necessary geometry data into the DBMS as required.
Open Data Source Command
Right clicking a component that is linked from an external data source in the Project pane and then choosing Open Data Source will open the Database Console and connect to the component data source.
Opening a component linked from an external data source in its own window, and then choosing the Open Data Source command in the component menu (for example, choosing Drawing - Open Data Source when a linked drawing window is open) does the same thing.
Invalid or Missing Geometry
At times we might encounter tables or the results of queries that contain invalid geometry. This might happen for a variety of reasons, such as a bug in a third party program that attempts to write geometry or even something as simple as damage to a data set. This is unfortunate, but it would be a shame to lose regular attribute information in such cases, or, even if there is not attribute information it would be a shame not to be alerted to the problem that some objects do not have valid geometry. Manifold will try to salvage what information it can.
Manifold deals with invalid geometry in tables or queries by replacing the invalid metric (that is, the invalid geometry) for each such object with a synthetic metric consisting of a single coordinate located at the (0,0) origin of the drawing's coordinate system. The effect of this is to create such objects with invalid geometry as points at the (0,0) location of the drawing.
For example, if we link a drawing from a table that supposedly contains only areas and we find that some points have appeared at the (0,0) origin of our drawing's coordinate system, we know that some of those areas had invalid geometry. If our drawing is in latitude / longitude, those points will appear all stacked over one another at the intersection of the Equator and the Prime Meridian in the ocean off Africa.
Why does Manifold use the (0,0) location? The points representing objects with invalid geometry have to be put somewhere and this location has the advantage of being easy to remember and, in many common projections, not usually occupied by something else. Experienced GIS users also know that various projection problems will often toss objects into a tiny speck off the coast of Africa so this helps us remember just where Manifold places objects with invalid geometry.
Is it inconvenient to put all points from objects with invalid geometry at the same spot? Not really, since it is easy to select them with a Select Box or other mouse selection method and then we can open the drawing's table, filter on the selected items and see what objects we have at that location.
"Multi-point" geometry values generated by AllCoords and several other query functions are handled the same way: the result of the query function will be a point at the (0,0) origin of the drawing.
When Manifold replaces invalid geometry with a synthetic point metric the IDs of the first ten objects with invalid geometry and a replaced metric will be logged into the History pane.
Limitations with IBM DB2
IBM DB2 Express-C Edition may be used for Enterprise Edition Enterprise Servers without any technical limitations. However, two small usage limitations apply when DB2 Express-C is used for storing geometry in tables within the database to support subsequent concurrent, multiuser editing. When a drawing is linked from a DB2 Express-C data source the following limitations apply:
· Adding a new object and immediately editing it without refreshing the drawing creates an editing conflict.
· Adding a new object and immediately deleting it without refreshing the drawing will fail.
Therefore, when ever editing drawings linked from a DB2 Express-C data source make sure to refresh the drawing after adding a new object before attempting to edit that object or to delete that object.
Notes
Refreshing a linked drawing created from a table that resides in an external data source will refresh the table. Refreshing a drawing linked from a query will recompute the query.
Refreshing a query will refresh all linked drawings descended from that query.
In the example above we could have created a table containing the geometry data in WKB form from the Mexico drawing using the SELECT … INTO construction in an action query:
SELECT CGeomWKB(Geom(ID)) AS [Geom], [Mexico_eg Drawing].*
INTO [WKB Table] FROM [Mexico_eg Drawing];
Tech Tip
Remember, ADO .NET connections are read-only. If we are link a drawing from a DBMS table using ADO.NET, we will not be able to edit the drawing. To link a drawing so it is editable we must use some read/write connection technology such as OLE DB or ODBC.
See Also
The above two topics cover the material in this topic from slightly different perspectives and should be read by anyone working with geometry in tables or drawings stored in a DBMS.