Re: Polygon data modelling question

"John Palmer" <jopalmer_at_mail.vt.edu> wrote in message news:<a24amu$qd$1_at_solaris.cc.vt.edu>...
> But a co-worker says that if you never want to only return one
> row, ie one point, then it shouldn't be a row and that storing coordinates
> individually is a common mistake people make and we should learn from them.
> Does anyone have any ideas on what I should do?

    Starting in the early 1990s, many DBMS products began to support the idea  of DBMS 'extensibility'. The idea is that a concept like a 'polygon' (or a  path, or a graph, or a rational number, or a complex number, or a Java class  corresponding to a mailing address) are modelled within the database as a  SQL data type (think relational domain (Hi Chris!)). All of that class/type/  domain's behavior is implemented by linking the appropriate shared library  (or jar file) into the DBMS and havings its functions invoked by the DBMS  in response to SQL queries issued by the application. For example:

   CREATE TABLE House_Plan_Elements (

         Element_Id      System_Generated_Key     PRIMARY KEY,
         Plan_Id         House_Plan_Id            NOT NULL,
         Label           String                   NOT NULL,
         Material        Material_Enum            NOT NULL,
         Element         Polygon_Set              NOT NULL

    "In the house plan for 'Fred and Wilma', show me all of the 'wooden'      elements within 2 feet of an 'electric' element."

    SELECT H1.Label, H1.Element,

           H2.Label, H2.Element
      FROM House_Plan_Elements H1, House_Plan_Elements H2
     WHERE H1.Plan_Id = 'Fred and Wilma'
       AND H1.Plan_Id = H2.Plan_Id
       AND H1.Material = 'Electric'
       AND H2.Material = 'Wooden'
       
       AND Distance ( H1.Element, H2.Element ) < '2 FEET';

    There is actually a huge amount going on in this query. First, the query   addresses 'Element' column values and uses a 'Distance' function/sub-routine/   method to compute the distance between them. For arbitrary polygons computing   minimum distance is not a trivial problem, particularly for large polygons   with many points. Second, the result of the Distance() function is not going   to be a simple 'double precision' data type. This is necessary because as   you can see the measure of distance involves both a unit (FEET) and a   quantity. Of course, just as a Polygon may have multiple public   representations, the Distance type here might just as easily have a simple   naked double precision representation that the DBMS 'knows' (because you   told it how) to convert into a distance measure with some default unit.

    Both free (PostgreSQL) and commercial (practically all of 'em except   Microsoft's SQL Server) products support these features. Some do it better   than others, mainly because some of 'em have been at it longer than others.   Postgres has been at it the longest. Postgres, in fact, has a complete set   of spatial data types and a reasonably good indexing implementation built in.

    So in a nutshell, the most efficient way to address your requirements, in   terms of operational efficiency (a good DBMS will have indexing to   support these extensions), data model flexibility (no more will you have   need to convert complex real world objects into a lower level set of   char arrays and numbers) and developer productivity, is to make use of this   kind of technique.

    But if you'd like, you can still just shove the data into a blob and   write all of this stuff outside the DBMS in a middleware layer.

    Hope this helps!

          KR

                     Pb