Interview by Nestoria on Real Estate Mapping
· 50 days ago by Simon Greener
This month, January 2009, I was interviewed by Kat Mackintosh, an Australian working for Nestoria in London. Nestoria are currently in the process of expanding their real estate portal into Australia.
The interview was mainly concerned with my involvement in a real estate web mapping system in 1997. It also covered my involvement with the original Telstra Whitepages / Yellowpages web mapping system (click on Show Map and select “39 Cliff view Drive…”).
From this base, the interview then looked at technological and data licensing issues with web mapping, as well as reflections on the future directions of web mapping and also cartography/graphics integration.
The interview can be found here
Mapping surface area of a ruptured pipe in Oracle Spatial
· 95 days ago by Simon Greener
I gave a talk last year entitled Automated Layer Management – Experiences At Mid Coast Water which was about mapping the area that would be affected by a mains pipe bursting in either a sewer or water network. (Yes it was a not very exciting title.) The mapping was all done in 2D as Oracle 10gR2 and before does not have any 3D capabilities that would help do the work in 3D.
The work also had many other aspects, the main one being that because the problem was clearly defined, described and attributed (eg invert levels below surface level), the solution could be fully automated via the use of very efficient trigger based processing.
The customer asked me to look at whether it would be possible to redo the solution such that it fitted seamlessly into the same framework as the current solution, but extend it to model the area that would be affected in 3D.
This involves modelling the pipe burst as a 3D solid and then calculating the intersection between it and a 3D model of the ground surface.
Since my customer is on 10gR2 I had to design a solution based on the lack of internal support for 3D solids, triangulated irregular networks etc. Also, the customer wants me to “look forward” to what is in 11gR1 and R2 that may help when an upgrade of the database software occurs.
Well, I came up with a solution that will actually run on SQL Server 2008 and PostgreSQL/PostGIS as both support linear and polygonal data with Z and M values.
The solution was presented at the Australia Oracle User Group (AUSOUG) annual conference Melbourne 16 – 17 November 2009. The title of the paper was – yes another bad title – Extending a 2D Zone of Influence to 3D using Oracle 10g and 11g SDO_TIN. (I changed the name of the PDF to make the document more descriptive of its contents.)
If anyone would like me to implement this solution for them in Oracle, PostGIS or SQL Server 2008 please feel free to email me.
FOSS4G 2009 Sydney Presentation
· 131 days ago by Simon Greener
I gave a rather manic presentation (Open Source, Oracle and PostGIS) at the FOSS4G 2009 Conference at Darling Harbour in Sydney, Australia, on Thursday October 22nd. The presentation was a little risqué a times (anyone offended please accept my apologies: I seem to become a different person when I present) and, I suspect, its message was somewhat hidden by the volume of slides and the speed of presentation.
This short note is an attempt to clarify what I was trying to do.
Oracle Spatial and PostgreSQL’s PostGIS are two of the most mature implementations of a spatial type system for their relevant host databases. One is open source, the other proprietary. Yet open source software supports both products with some open source products finding aspects of Oracle’s implementation of standards problematic.
PostGIS’s function set for its basic spatial type is far more extensive than Oracle’s. As each release is made, PostgreSQL/PostGIS increases in strength with EnterpriseDB aiming to do the impossible, which is to hide the differences between Oracle and PostgreSQL in order to convert businesses from Oracle to PostgreSQL.
In my working career I have rarely seen, on a customer’s servers, only ONE DB product. The geospatial professional thus has to learn to work with all databases that support a spatial type: you can’t demand the removal of one or other of the databases! You have to learn to work with both.
In order to improve open source software support for Oracle, and to help the geospatial professional manage both databases at the one site, the talk I gave tried to provide:
- An understanding of Oracle Locator/Spatial concepts and components;
- The relevant OGC/SQLMM standards in common;
- How to bring the separate geospatial metadata structures together;
- An understanding of the type systems of Oracle SDO_Geometry/ST_Geometry and PostGIS’s geometry;
- Oracle’s tolerance model (as this impacts how one can port PL/SQL or PL/pgSQL code from one database to the other);
- Programmatic and framework issues.
Once these were described I hoped that the audience would be aware of the issues when migrating between the databases. It was also intended to show how to minimise the amount of code that would need to be rewritten when deploying a custom function on both databases.
The talk was not meant to be a promotion of Oracle. Rather it was meant to foster improved access by open source software (eg ogr2ogr) and to help when called upon to manage both Oracle Spatial and PostGIS installations.
GIS software and Database Primary Keys
· 302 days ago by Simon Greener
There has been some discussion over on the PostGIS email list about the use of primary keys on tables by GIS client software such as uDig, QGis, MapInfo etc. It is a discussion that comes up now and again (perennial, I suppose) especially when new users of PostGIS (or Oracle on the Oracle forum etc) start asking questions.
So, what are primary keys, why are they important to database tables and GIS software, and what limitations exist?
What is a primary key and its importance to a database table?
Firstly, we need to be aware of the notion of a candidate key.
A candidate key is a combination of attributes [When multiple attributes are used in a candidate key, it is also called a composite key] that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys.
In addition, we note that a candidate key:
… can either be part of the actual record itself [eg the unique attribute triplet – “customer_name,date_of_birth,sex”] or it can be a single artificial field (one that has nothing to do with the actual record eg object_id).
So, a table can have many candidate keys, composed of one or more attributes, and but that all have to be unique.
Finally, from a list of unique candidate keys for a table ONE is selected to be the table’s primary key. So, depending on design.
… a table may have arbitrarily many unique keys but at most one primary key.
Example of Defining a Table with a Primary and Unique Key
Here is a PostGIS example of how to define a table with geometry data, a single primary key (on the column id) and an alternate unique (candidate) key (on column parkname via a UNIQUE constraint).
DROP TABLE parks;
Query returned successfully with no result in 62 ms.
CREATE TABLE parks
(
id serial NOT NULL,
parkname character varying(100),
park_type character varying(1),
suburb_id integer,
lastinsp date,
geom geometry,
CONSTRAINT parks_pkey PRIMARY KEY (id),
CONSTRAINT parkname_ukey UNIQUE (parkname),
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL)
)
WITH (
OIDS=FALSE
);
NOTICE: CREATE TABLE will create implicit sequence "parks_id_seq" for serial column "parks.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parks_pkey" for table "parks"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "parkname_ukey" for table "parks"
Query returned successfully with no result in 250 ms.
-- Since we have declared the geometry CHECK constraints ourselves, we can insert the spatial metadata into the geometry_columns table rather than use the AddGeometryColumn function.
--
insert into public.geometry_columns (f_table_catalog,f_table_schema,f_table_name,f_geometry_column,coord_dimension,srid,type)
values (' ','public','parks','geom',2,28355,'POLYGON');
Query returned successfully: one row with OID 395776 inserted, 63 ms execution time.
This table can now be opened in most GIS systems. If, however, instead of using a meaningless valued column, id, we had chosen to use just the parkname column as the primay key, no GIS client package around today would be able to work with this table even though the park name values held in parkname are actually unique.
-- Executing query:
DROP TABLE parks;
Query returned successfully with no result in 16 ms.
CREATE TABLE parks
(
parkname character varying(100),
park_type character varying(1),
suburb_id integer,
lastinsp date,
geom geometry,
CONSTRAINT parks_pkey PRIMARY KEY (parkname),
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'MULTIPOLYGON'::text OR geom IS NULL)
)
WITH (
OIDS=FALSE
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "parks_pkey" for table "parks"
Query returned successfully with no result in 141 ms.
Finally, what if the table had two or more columns in the primary key? The table would also not be able to be dealt with by most/all GIS software around today. Here is an example of a table with a two column primary key.
DROP TABLE cities;
Query returned successfully with no result in 16 ms.
CREATE TABLE cities (
name text,
population float,
altitude int,
state char(2),
geom geometry,
CONSTRAINT cities_pkey PRIMARY KEY (name,state),
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
)
WITH (
OIDS=FALSE
);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
Query returned successfully with no result in 110 ms.
The only way we can work with this sort of table is to add a unique integer field (in the table following I have called it gid as in geographic-id) to them and hope that the GIS will allow you to work with the defined, unique field. (For example, ArcSDE would allow this as it simply requires a UNIQUE index on a the integer field, but MapInfo, which requires Primary Keys, may not – I do not have a copy of MapInfo to check this.)
DROP TABLE cities;
Query returned successfully with no result in 0 ms.
CREATE TABLE cities (
name text,
population float,
altitude int,
state char(2),
geom geometry,
gid serial not null,
CONSTRAINT cities_pkey PRIMARY KEY (name,state),
CONSTRAINT cities_gid UNIQUE (gid),
CONSTRAINT enforce_dims_geom CHECK (ndims(geom) = 2),
CONSTRAINT enforce_geotype_geom CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
)
WITH (
OIDS=FALSE
);
NOTICE: CREATE TABLE will create implicit sequence "cities_gid_seq" for serial column "cities.gid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cities_pkey" for table "cities"
NOTICE: CREATE TABLE / UNIQUE will create implicit index "cities_gid" for table "cities"
Query returned successfully with no result in 110 ms.
This discussion applies to Oracle, SQL Server, MySQL etc. The limitation is not in the database product but, as is usually the case, in the GIS product.
I hope this is of use to someone.
>>A Thank You
SpatialDB Advisor
