Example: Create a Drawing Dynamically from a Geocoded Table

The Example: Create a Drawing from a Geocoded Table topic shows how to add a geometry field to a geocoded table, so we can then create a drawing from that table.  

 

In this topic, we show a more general, cooler way to create a drawing from a geocoded table, without adding any fields to the table.   We create a small query that generates geometry on the fly, and then we create a drawing from the query.   This is how more advanced users often do it, using a technique that is perfect for creating drawings from tables in remote DBMS packages or read-only files.

 

A geocoded table has records with a latitude and longitude for each record.   To create a drawing, we need a field for each record that is a geometry field, containing a point, line or area object.   To create a drawing from a geocoded table we must create a geometry field from the latitude and longitude fields.  The geometry field will contain a point object at the latitude and longitude location specified.   In this example we will write a query that creates a geometry field dynamically, on the fly, from the latitude and longitude fields.

Create a Query

This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city:

 

 

For each record there is a Longitude field and a Latitude field, both float64 numeric fields, that provide the longitude and latitude where the city is located.   We will create a query that contains SQL that dynamically creates a geometry field from the Longitude and Latitude fields.

 

From the main menu, choose File - Create - New Query  (or, right-click into the Project pane and choose Create - New Query):

 

 

In the New Query dialog, we provide the name Cities_geom and press Create Query.  There is nothing special about the name except that it is short, and it reminds us what the query does.   A new query appears in the Project pane.  

 

We double-click the new query to open it in a Command Window.   We enter the following SQL into the query:

 

 

The query text used in the illustration above is:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

), TRUE);

 

The above query is our basic template, that we can adapt and use in different situations for dynamically creating geometry from geocoded tables.  A discussion of how the query works appears later in this topic.

 

If we run the query by pressing the ! button, we see it generates a results table that is the Cities table with a Geom field added.  

 

 

The Geom field is shown with a gray background because it is a read-only field, calculated on the fly from the Latitude and Longitude fields.  It uses the same expression discussed in the Notes to the Example: Create a Drawing from a Geocoded Table topic.

 

It is a good idea to test a query that we write by running it, to make sure it creates what we want and does not fail with any error messages caused by typos or other errors.

Create a Drawing from the Query

Now that we have a query which dynamically creates geometry from latitude and longitude fields, we can create a drawing from that query, just as if it was a table.

 

 

We right-click onto the Cities_geom query and choose Create - New Drawing.

 

 

We have not yet told the drawing what coordinate system to use, so it reminds us of that by showing a placeholder in red color.  

 

We click on the coordinate system picker button, and choose Latitude / Longitude from the default list of favorites that appears.    Our table provides latitude and longitude locations for each city, so that is the coordinate system we should use.

 

 

Press Create Drawing.   A new drawing is created in the Project pane.

 

 

We can create a new map using a Bing image server satellite layer as a base layer with the Cities_geom Drawing as a layer..  We Style the points in bright color to get the display above.

 

The geometry for the points is being dynamically created on the fly based on the Latitude and Longitude values in the table.  If we change the Latitude and Longitude values for a particular record, the geometry will automatically be re-calculated by the query and the point will move.

 

 

In the illustration above, we have zoomed into Florida and have Alt-clicked the dot for Miami.

 

 

This opens the record for Miami in the Info pane.   We can double-click into the Latitude field and change the latitude value to 28, two degrees to the North of the value previously used for Miami.  We press Update Record to apply the change.

 

 

The dot for Miami immediately moves two degrees North, placing Miami in the ocean.  

 

We have completed our task, creating a drawing dynamically from a query.  This same technique is useful in many situations where we have a geocoded table and we want to create a drawing from that table.  For example, we may have a table in a database that lists the locations of vehicles we are tracking by latitude and longitude, with the values automatically being updated by some program that acquires reports from GPS receivers in those vehicles.  

 

Next, this topic discusses how the query works, so that instead of being something magical from a distant land where people speak a foreign language, it can become something we understand, find easy to use, and can adapt with confidence to different situations.  Following a discussion of how the query works we adapt our query to use with a read-only table stored in an external PostgreSQL database.

How the Query Works

Let us unpack the query used to create a table with geometry, to see how it works.   We begin by adding some extra white space to show better the different parts of the query:

 

TABLE CALL TableCacheIndexGeoms((

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

 

), TRUE);

 

The heart of the query is the SELECT statement:

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

 

That simply selects all of the fields from the Cities table, and in addition it uses the GeomMakePoint function to generate a computed field, a geometry point,  from the Latitude and Longitude fields.  The AS aliasing word names that derived field Geom.   

 

Let us take the query apart into even simpler pieces.    We could run a very simple SELECT statement to fetch whatever fields we want from the Cities table, such as:

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude]

  FROM [Cities];

 

Pressing ! to run that results in:

 

 

But a SELECT list of fields is not limited to only those fields which exist in a table.  The SELECT list can include expressions, for example, computed fields.   For example, we could write:

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    [Longitude] + [Latitude] AS [Weird Sum]

  FROM [Cities];

 

We have continued the SELECT list on a second line, to avoid the query getting too wide to fit into a small illustration.   We can use white space as we like in SQL for better legibility so there is no harm done splitting up long lists into more than one line.   

 

It is a weird thing to do to add longitude and latitude, but we do it anyway just to show we can do whatever we want in a computed field.   If we did not use the AS aliasing operator, the column would have be called Result instead of Weird Sum:

 

 

In our query we would like to do something more useful than just adding latitude and longitude together.   We will use the VectorMakeX2 function within the GeomMakePoint function to create point geometry out of the latitudes and longitudes in our computed field.  We name the result Geom since that is a traditional name for Manifold geometry fields.  

 

The expression...

 

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

 

...is the same expression the Transform template uses in the Example: Create a Drawing from a Geocoded Table topic, as shown in the Notes section of that topic.  The notes show how we can use Manifold to write the SQL for us if we do not remember the right expression to create geometry from latitude and longitude fields.  

 

Plugging the desired expression into our simple SELECT statement, we get:

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities];

 

Running that, we get:

 

 

We have created a query that dynamically creates point geometry from latitude and longitude numbers.  Why not just create a drawing from that query?  Why do we wrap it within a TableCacheIndexGeoms function?

 

The answer is that we could create a drawing from the above query.   However, no spatial index is created by the above query, so every time we opened the drawing it would open up blank with a message from Manifold saying there is no spatial index and asking if we want to build a temporary spatial index.   That is annoying, and something we can avoid by using the TableCacheIndexGeoms function.    

 

Reading the SQL Functions topic, we see that the TableCache(<table>, <writable>)  function and the TableCacheIndexGeoms(<table>, <writable>) function provide cached access to a table's data, allowing reads and writes through the cache to the originating table, or not, depending on whether we set the <writeable> argument to TRUE or FALSE.  Caching the table's data allows elegant and fast use of the table's data in settings where we might want to generate an index on the fly, like creating a spatial index for a geometry field that we calculate but which is not in the original table.   The TableCacheIndexGeoms(<table>, <writable>) function automatically generates a spatial index for us on any geometry field in the table.

 

Written in the indented, multi-line text style we are using for our query, the framework for using TableCacheIndexGeoms is:

 

TABLE CALL TableCacheIndexGeoms(

<table>

, TRUE);

 

Why TABLE CALL?   A query always returns a table.  To write a valid query the end result has to be a table.  The usual choice is to write a query that is a SELECT statement, since SELECT statements always result in a table, but if the final, outer, onion layer of our query is a function, like a TableCache function, we can use the TABLE statement as the final step in the chain of wonderfulness our query accomplishes.  TABLE CALL executes a CALL to a function that returns a table.  In this case, we execute a CALL to the TableCacheIndexGeoms function.  

 

That function in turn, as seen in the above framework, is looking for a table on which it will operate.  In this case, the table we will feed it is the table that results from the SELECT statement we use.   We wrap our SELECT statement in parentheses:

 

(

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

)

 

Putting that into our TableCacheIndexGeoms framework, we get the query we use:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

), TRUE);

 

The key to understanding most SQL is to break down what may be a lengthy SQL query into simpler sub-units, and then understanding what each piece does.

Variations

We can easily adjust the query for different situations.  

 

For example, if we dynamically generate geometry from a read-only table in a remote data source, we would set the TableCacheIndexGeoms <writeable> argument to FALSE, as in:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

), FALSE);

 

If our originating table is read-only, we still will not be able to write into it even if we set the <writeable> argument to TRUE, but setting it to FALSE is a good idea in any event.

 

In our example table, the longitude and latitude fields are floating point numbers, of data type float64.    But in many geocoded tables the longitude and latitude fields are text fields, such as nvarchar or varchar data types, and are not numeric fields.    Those will not work in our query, because the VectorMakeX2 function:

 

VectorMakeX2([Longitude], [Latitude])

 

expects numeric arguments.  We can adjust for that by using the CAST SQL operator.  CAST is a universal way of converting data types on the fly in a query.  For example, instead of writing

 

[Longitude]

 

We would write

 

CAST([Longitude] AS FLOAT64)

 

CAST as used above converts a text Longitude field value into a numeric, float64 value.   We would re-write the VectorMakeX2 expression as

 

VectorMakeX2(CAST([Longitude] AS FLOAT64), CAST([Latitude] AS FLOAT64))

 

The result in the final query ends up using many layers of parentheses:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2(CAST([Longitude] AS FLOAT64), CAST([Latitude] AS FLOAT64))

    ) AS [Geom]

  FROM [Cities]

), TRUE);

 

The above query works if the Longitude and Latitude fields are text fields.   It also works if the fields are numeric fields, so some people will always use a CAST, just in case the table they are working with uses text strings to store what should be numbers.  

 

If the longitude and latitude fields are numbers, there is no performance downside to an unnecessary CAST: the Manifold query engine optimizes queries, so instead of casting a floating point number into a floating point number, it just ignores the unnecessary CAST.

Recycle the Query for a Different Table

We can use the same basic query over and over in different situations.   

 

Suppose, for example, we have a read-only table stored in a PostgreSQL database that gives the latitude and longitude locations of mines, bridges, tunnels, oil fields, dams, and tunnels in New Mexico:  

 

 

The table has no key field and index, so it is read-only and thus shown in gray background color.   We would like to create a drawing in our Manifold project that is based on the geocoded table stored in PostgreSQL, but we want to see only mines.   

 

We create a query called nm_mines_postgres and double-click it open in a Command Window, entering the following SQL:

 

 

The query text used in the above illustration, formatted to fit into the Command Window, is:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [Name], [Elevation], [County],

    GeomMakePoint(VectorMakeX2( CAST([Longitude] AS FLOAT64), CAST([Latitude] AS FLOAT64) )) AS [Geom]

  FROM [Postgres]::[public.gnis_nm]

  WHERE [Class] = 'Mine'

), FALSE);

 

It is basically the same query as used earlier in the topic.  

 

We SELECT the fields we want to see in the result table, and we do CAST type conversions since the latitude and longitude fields are nvarchar text fields.  We also set the <writeable> argument for the TableCacheIndexGeoms function to FALSE, since we want this to be a read-only use where we do not want to accidentally change anything in the originating Postgres table.   If we want to read/write/select in the dynamically created drawing, and thus allow changes in the originating Postgres table, we would set it to TRUE.

 

The name of the table in the FROM clause is more complicated, because that is the notation used to refer to a table within a data source.  If we do not remember how that notation should be written, no problem.  We simply ask the query builder to write it for us by double-clicking onto the name of the table we have dragged into the lower right pane of the Command Window.

 

We have also added a WHERE clause:

 

  WHERE [Class] = 'Mine'

 

That filters the table resulting from the SELECT statement to only those records which are mines.    Running the query shows the expected result:

 

 

We can create a drawing from the query, using the same procedure as shown before in this topic:

 

 

If we like, we can adjust the query to show only those mines that have the word Uranium in their names.   We Copy the query, Paste it in the Project, rename it slightly and then reopen the renamed query in a Command Window, to make a quick edit, adding an AND qualifier:

 

 

Our query text is now:

 

TABLE CALL TableCacheIndexGeoms((

  SELECT [Name], [Elevation], [County],

    GeomMakePoint(VectorMakeX2( CAST([Longitude] AS FLOAT64), CAST([Latitude] AS FLOAT64) )) AS [Geom]

  FROM [Postgres]::[public.gnis_nm]

  WHERE [Class] = 'Mine'

    AND StringContains([Name], 'Uranium')

), FALSE);

 

We have added an AND qualifier, using the StringContains function to filter the results table to only those records that are mines and which also have the word Uranium in the name of the mine.  Running the query shows the results:

 

 

We now have a query that generates a table with a geometry field in it, where all of the records are mines with the word Uranium in their names.    As before, we create a drawing from that query, and we add it to the map.

 

 

We have used Style to show the uranium mines as bright magenta squares.     We can label them by right-clicking on the nm_u_mines_postgres Drawing and choosing Create - New Labels to create labels for the drawing.

 

 

We have created labels using the Name field of the component, and dragged and dropped the labels component into the map as seen above, styling the labels so they appear in white text above the satellite Bing layer.  Everything works as expected, no matter that the labels are created for a drawing that was, in turn, created from a query that pulls desired data from a read-only table in a remote PostgreSQL database.  

 

 If the table in the PostgreSQL database changes, when the drawing is refreshed in Manifold, the drawing and the labels will be based on the changed table as well.   This is a great reason to use queries to generate geometry for drawings that we would like to be based on data in external databases.

 

 

Taking a look at the Project pane, we can see the various components we created in this topic.  Except for the Cities table and the public.gnis_nm table within the Postgres data source (not visible since we have closed the Postgres data source hierarchy to allow for a smaller illustration), all of the other components we have added in this topic.  

 

When we save this project it will be tiny, no larger than the small Cities table, since the project itself contains no data: all of the data for the mines and other objects in the  public.gnis_nm table is stored in the PostgreSQL database outside of Manifold.   We could have millions of locations stored in that table, and the project would still be tiny, about one megabyte, in size, not tens or hundreds of megabytes.  That is because the drawings are based on queries, which generate results tables on the fly from the source tables.

Notes

How to learn the SQL used in this topic - This topic takes apart the query used to show how it works, but we still need to know about functions the query uses.  How do we learn about those functions, short of pouring through hundreds of functions in the SQL Functions topic?   We learn about them by reading example topics like this one, and like the Example: Create a Drawing from a Geocoded Table topic, including the Notes at the end.  We also learn about them by trying something similar in the Transform pane and pressing the Edit Query button to see what SQL the system will write.  We can then look up the functions Manifold used in the SQL it wrote for us, to understand how they work.

 

About those Longitude and Latitude fields -  The inner SELECT statement in our query is:

 

  SELECT [mfd_id], [Place name], [Longitude], [Latitude],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

 

We do not need to include the Longitude and Latitude fields in the SELECT list to make the GeomMakePoint function work.  The function knows those fields are in the table and can use them without our needing to copy them into the results table.   We could have written the query as:

 

  SELECT [mfd_id], [Place name],

    GeomMakePoint(VectorMakeX2([Longitude], [Latitude])) AS [Geom]

  FROM [Cities]

 

The result would still have geometry created by the GeomMakePoint construction.    So why include the Longitude and Latitude fields in the SELECT list?  We do that simply to provide all of the fields in the original table as attributes in the drawing, in case any user wants to Alt-click on a city point in the drawing to see it in the Info pane.

 

Server-side queries -  Manifold can execute queries within the PostgreSQL server as well as within the local Manifold project, and we can combine the two for more efficient work.  For example, instead of writing a single query in the project which filters records to only those for mines that have Uranium in their name, we could have written a query within the PostgreSQL server that first filtered the table for only those records.  Our Manifold query could then take only those records to build a geometry field for them, so a drawing could be created.  If we have very large tables it is usually more efficient to use the resources of the server to filter them down to only the records we want.  Manifold is very fast with large tables, but if the big data tables are stored within a DBMS server to which we must connect through a network, fetching records that are not needed from that server only to filter them within Manifold for big tables will not be as efficient as doing filtering on the server and sending only desired records over the network to Manifold.

 

Making a table read/write - The PostgreSQL table we used  in this example, that shows mines and other points of interest, is a read-only table because it has no key field and index.  

 

 

It appears with gray background color for all fields, to indicate they are read-only.    We can make the table read/write by adding an identity field and btree index.

 

To do that, with the focus on the opened table, we launch Edit-Schema, we press the Add Identity button and then we press Save Changes.  

 

 

The background color for all columns except the new ID column turns white, to show they are all now read/write.  The new ID column (barely visible on the right edge of the table in the above illustration) is shown with gray background color because it is a read-only column.    We can open the Edit-Schema dialog to see why.

 

 

Manifold knows that a key field can be created in PostgreSQL by creating it as an autogenerated column, which makes it a field computed by PostgreSQL and thus read-only.   Manifold also creates the constraint that the ID field cannot be a NULL.  

 

See Also

Tables

 

Schema

 

Drawings

 

Queries

 

Info Pane

 

Command Window

 

SQL Functions

 

Example: Multiple Drawings from the Same Table - Illustrates how easy it is to create multiple drawings that use the same table and same geometry by copying and pasting an existing drawing.  Each new drawing takes no additional storage space in the project, but can be formatted differently.   

 

Example: Create a Drawing from a Geocoded Table - A partner example to Example: Create a Geocoded Table from a Drawing   A geocoded table has records with a latitude and longitude for each record.   This example starts with a table containing a list of cities with a latitude and longitude field for the location of each city.   We create a geom from the latitude and longitude fields using a template in the Transform pane and then we create a drawing that shows the cities as points.  This example shows all the infrastructure steps involved.

 

Example: Create a Drawing from Read-Only Geocoded CSV File - A detailed example using a mix of dialogs and SQL to create a drawing that shows data from an external, read-only geocoded CSV file that is linked into the project.  

 

Example: Add a Spatial Index to a Table - A typical use of an index is to provide a spatial index on a geom field in a table, so the geom data can be visualized in a drawing.  This example shows how to add a spatial index using a geom field in a table.

 

Example: Create a Geocoded Table from a Drawing - A partner example to this topic.  A geocoded table has records with a latitude and longitude for each record.   This example starts with a table for a drawing of points where the geom field in the table contains geometry information for each point.   We extract the Y and X locations for each point  from the geom field to create latitude and longitude fields in the table for each record.

 

Example: Drawings use Geom Fields in Tables  - An essential discussion on how drawings are created from geom fields in tables, including how the drawing knows which coordinate system to use.

 

Example: Two Drawings from the Same Table - Take a table with a geom field that is visualized by a drawing.  Add a second geom field to the table and create an rtree index on that field so it can be visualized by a drawing.   Copy the first drawing, paste it and adjust the pasted copy so it uses the second geom field. Demonstrate how to use the Transform pane to show "live" modifications in the second drawing compared to the first drawing.