Re: [udig-devel] Reference for table join

[Craig Taverner <craig@xxxxxxxxxx>]

My understanding from reading the introduction to the referenced "OGC Calls for Comment on Table Joining Service Standard" is that they are talking primarily about remote table joins. Meaning joining tables from different remote databases. So presumably this is a much more difficult operation since it cannot rely on the internal join optimizations of the database itself. It implies that a wrapper service sits above the two databases performing the join on the fly, probably maintaining an internal cache of index/keys to make sure performance is optimized, especially for subsequent queries.

This is quite interesting to me in two ways:

• The wrapper service would allow joins between relational and nosql databases, which is something likely to become more common as spatial nosql starts becoming more popular.
• The wrapper service might be easy to write using a database like Neo4j that often needs to deal with correlations to other, relational, databases. For example, the internal cache/index that would be necessary to optimize the join could be built as a tree graph in neo4j on the fly. The actual data would still be in the two remote databases, but the index connecting the two together (providing the optimized join) would be in the local graph. This would scale, since the local graph would be in an on-disk database with partial in memory caches.

Certainly an interesting discussion I think!

On Fri, Nov 12, 2010 at 9:10 AM, andrea antonello <andrea.antonello@xxxxxxxxx> wrote:
>
> Hi Jody,
> it might be a language barrier problem of mine, but I do not
> understand exactly what you write.
>
> To me table join is for example linking two tables by a common
> attribute. The most common case, which has also been asked dozens of
> times in this list or by uDig users, is that of being able to joint
> two postgis tables Aspatial & Bnonspatial by a common field and for
> example view it as one (join) in the table view.
> This obviously would also introduce the possibility of multiplicity,
> i.e. there might be several records in Bnonspatial that link to a
> single record in Aspatial.
>
> Alright, would you mind to repeat your thought of the last email with
> this example in mind?
>
> Thanks man,
> Andrea
>
>
>
>
> On Fri, Nov 12, 2010 at 4:04 AM, Jody Garnett <jody.garnett@xxxxxxxxx> wrote:
> > Afternoon
> >
> > I have accomplished table joins (with the udig reshape operation) in
> > the past by defining a custom function ... the thought being:
> >
> > 1. function can load in the target csv file
> > 2. function can take the external key as an argument
> > 3. look up the correct row; and return the requested attributes
> > 4. as long as the taget file is cached in memory performance can go pretty well
> >
> > This has been used by the app schema team to some effect; for joining
> > property files etc...
> >
> > I had the following sent in my direction; and may serve as a reference
> > point if we want to look into a more serious solution.
> >
> > Jody
> >
> > ---------------------------------------
> > From: OGC Press <announce@xxxxxxxxxxxxxxxxxx>
> > Reply-To: <announce@xxxxxxxxxxx>, <announce@xxxxxxxxxxxxxxxxxx>
> > Date: Tue, 16 Mar 2010 10:53:31 +1000
> > To: <media@xxxxxxxxxxxxxxxxxxxxxxxx>
> > Subject: [OGC Press Release] OGC Seeks Comments on Table Joining
> > Service Standard
> >
> >
> > PRESS ANNOUNCEMENT FOR IMMEDIATE RELEASE
> > For information about this announcement, contact:
> >
> > Lance McKee
> > Outreach Consultant
> > Open Geospatial Consortium, Inc
> > tel: +1-508-655-5858
> > outreach@xxxxxxxxxxxxxxxxxx
> >
> > -----------------------------------------------------------------------
> >
> > Wayland, Mass., 15 March, 2010 - The Open Geospatial Consortium, Inc.
> > (OGC®) seeks public comment on the draft OGC Table Joining Service
> > (TJS) Interface Standard. This standard defines an interface for
> > services that provide the ability to join attribute data stored in one
> > database on a network with the corresponding geometry (points, lines,
> > or polygons) stored in another network accessible database.
> >
> > For example, a table on one server may indicate the population of
> > various cities, while a second server may contain the geometry that
> > describes the cities' locations and boundaries. The TJS standard
> > describes a set of interfaces for both servers that would allow the
> > city name to be used as the "common geographic identifier" in order to
> > join the population data to its geometry, thus enabling the user to
> > create a map or perform geospatial analysis on the tabular data. An
> > earlier draft of this specification was called the "Geographic Linkage
> > Service".
> >
> > The proposed OGC TJS 1.0 standard and information on submitting
> > comments on this document are available at
> > http://www.opengeospatial.org/standards/requests/62.  The
> > public comment period closes on 14 April, 2010.
> >
> > The OGC is an international consortium of more than 390 companies,
> > government agencies, research organizations, and universities
> > participating in a consensus process to develop publicly available
> > geospatial standards. OGC Standards support interoperable solutions
> > that "geo-enable" the Web, wireless and location-based services, and
> > mainstream IT. OGC Standards empower technology developers to make
> > geospatial information and services accessible and useful with any
> > application that needs to be geospatially enabled. Visit the OGC
> > website at http://www.opengeospatial.org.
> > _______________________________________________
> > Press and Media mailing list
> > Open Geospatial Consortium, Inc.
> > 35 Main Street, Suite 5, Wayland, MA 01778 USA
> > Media@xxxxxxxxxxxxxxxxxxxxxxxx
> > Subscribe/unsubscribe for OGC lists at
> >  http://www.opengeospatial.org/resources/?page=email
> > Other List Options available at
> >  https://lists.opengeospatial.org/mailman/listinfo/media
> > _______________________________________________
> > User-friendly Desktop Internet GIS (uDig)
> > http://udig.refractions.net
> > http://lists.refractions.net/mailman/listinfo/udig-devel
> >
> _______________________________________________
> User-friendly Desktop Internet GIS (uDig)
> http://udig.refractions.net
> http://lists.refractions.net/mailman/listinfo/udig-devel