Dilemma: is gid really useful in a geoDB?

[Antonello Moneta]

Hi list,

I have a dilemma: is gid really useful in a geoDB?
I am building a geodb for several harbor. When I create a layer(table
with geometry) in postgis, I need a gid like a primary key, or I can use
a different one? Can I  use several field of from the table like a
primary key?

I am not new in the gis, but why in almost all geoDB I saw there is a
gid?! Is it really necessary? Do it come from shapefile standards?

For example QuantumGIS doesn't work if the layer has only one primary
key, there are other application that don't work like qGis?

Thanks a lot, Antonello
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Stephen Woodbridge]

Antonello wrote:

> Hi list,
>
> I have a dilemma: is gid really useful in a geoDB?
> I am building a geodb for several harbor. When I create a layer(table
> with geometry) in postgis, I need a gid like a primary key, or I can use
> a different one? Can I  use several field of from the table like a
> primary key?
>
> I am not new in the gis, but why in almost all geoDB I saw there is a
> gid?! Is it really necessary? Do it come from shapefile standards?
>
> For example QuantumGIS doesn't work if the layer has only one primary
> key, there are other application that don't work like qGis?
>

Hello Antonello,

There is basically a requirement to have some kind of unique way of
accessing any given record. This is so programs can update the record or
fetch the records later based on a unique id. Also some systems might
expect that the uid be an integer. So adding the gid column is a
convenience.

If you know your usage patterns and you know the requirements for the
software you are using with the database, then you can probably safely
remove or change it to something else. I don't think gid needs to be the
primary key if you have something else, you could just make the gid a
unique column.

-Steve
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paul Ramsey]

Correct, almost all client applications require a primary key.  Many
require that the primary key be a single column.  shp2pgsql creates a
'gid' automatically, but you could always drop it if you wanted some
other primary key.

Stephen Woodbridge wrote:

> Antonello wrote:
>> Hi list,
>>
>> I have a dilemma: is gid really useful in a geoDB?
>> I am building a geodb for several harbor. When I create a layer(table
>> with geometry) in postgis, I need a gid like a primary key, or I can
>> use a different one? Can I  use several field of from the table like a
>> primary key?
>>
>> I am not new in the gis, but why in almost all geoDB I saw there is a
>> gid?! Is it really necessary? Do it come from shapefile standards?
>>
>> For example QuantumGIS doesn't work if the layer has only one primary
>> key, there are other application that don't work like qGis?
>>
>
> Hello Antonello,
>
> There is basically a requirement to have some kind of unique way of
> accessing any given record. This is so programs can update the record or
> fetch the records later based on a unique id. Also some systems might
> expect that the uid be an integer. So adding the gid column is a
> convenience.
>
> If you know your usage patterns and you know the requirements for the
> software you are using with the database, then you can probably safely
> remove or change it to something else. I don't think gid needs to be the
> primary key if you have something else, you could just make the gid a
> unique column.
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users

--

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   pramsey@...
   Phone: [phone]
   Cell: 250-885-0632
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Markus Schaber-7]

Hi, Antonello,

Antonello <antonello.monetsen@...> wrote:

> I have a dilemma: is gid really useful in a geoDB?
> I am building a geodb for several harbor. When I create a layer(table
> with geometry) in postgis, I need a gid like a primary key, or I can use
> a different one? Can I  use several field of from the table like a
> primary key?
> I am not new in the gis, but why in almost all geoDB I saw there is a
> gid?! Is it really necessary? Do it come from shapefile standards?

Technically, there's no need for a gid, from the database point of
view, as SQL deals with sets of rows.

However, most applications want some way of identifying specific rows,
especially when updating / editing data.

GID is generated by shp2pgsql.

> For example QuantumGIS doesn't work if the layer has only one primary
> key, there are other application that don't work like qGis?

QuantumGIS has the additional oddity that the primary key has to be of
type int4 - int8 or serial (which is optimal for primary keys) is not
accepted, AFAIK.

For read-only access, you can try MezoGIS which makes very little
assumptions about your database, and you can specify layers via
arbitrary queries.


Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Marc Compte]

Hi,

Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value)
in a relational data base is guaranteed to be logically accessible by
resorting to a combination of table name, primary key value and column
name."?

I find it surprising to see that "there's no need for a gid, from the
database point of view" and that primary keys are only needed because
"most applications want some way of identifying specific rows", as the
very core definition of the relational model clearly identifies this as
a crucial part of the model. Wanting to identify specific rows is not an
application oddity, it's part of the very definition of the model.

Every table needs to have a key, not only to conform with the relational
model's theory, but for pragmatical purposes as well. Even (or
specially) from the database point of view, no key means there is no way
to update a unique field, there is no way to extract the information
about one individual record, there is no way to cross-reference tables
... in short, no key means something like there is no relational
database, only a flat list of data ... and PostgreSQL, even though it
incorportes OO elements, has still a big part of relational, right?
 
Whether you have to be using that particular field (gid) or not depends
on your data. I don't think naming your key field "gid" is defined in
any standard (it shouldn't be anyway). As other people said, shp2pgsql
automatically generates this one for you, but I assume that is just to
make your life easier and make sure there is at least one way to
uniquely identify records.

The only important thing to do should be to make sure that there's at
least 1 field with values that can be used to uniquely identify any of
the records of your table. If you are sure there's another field (or
combination of fields) that serves the purpose right, you wouldn't
actually need to have that particular gid field. You should be able to
delete it and still be able to use on that table all of the features of
the database and any other surrounding application by using your own
self-made primary key.

If an application has a problem with any of this (multiple field keys,
using a different name for the key field other than gid, ...) then you
could probably say it is not well designed and you might want to switch
to something else.

Marc

En/na Markus Schaber ha escrit:

> Hi, Antonello,
>
> Antonello <antonello.monetsen@...> wrote:
>
>  
>> I have a dilemma: is gid really useful in a geoDB?
>> I am building a geodb for several harbor. When I create a layer(table
>> with geometry) in postgis, I need a gid like a primary key, or I can use
>> a different one? Can I  use several field of from the table like a
>> primary key?
>> I am not new in the gis, but why in almost all geoDB I saw there is a
>> gid?! Is it really necessary? Do it come from shapefile standards?
>>    
>
> Technically, there's no need for a gid, from the database point of
> view, as SQL deals with sets of rows.
>
> However, most applications want some way of identifying specific rows,
> especially when updating / editing data.
>
> GID is generated by shp2pgsql.
>
>  
>> For example QuantumGIS doesn't work if the layer has only one primary
>> key, there are other application that don't work like qGis?
>>    
>
> QuantumGIS has the additional oddity that the primary key has to be of
> type int4 - int8 or serial (which is optimal for primary keys) is not
> accepted, AFAIK.
>
> For read-only access, you can try MezoGIS which makes very little
> assumptions about your database, and you can specify layers via
> arbitrary queries.
>
>
> Regards,
> Markus
>
>  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Havard Tveite]

In the relational model, the primary key can be a combination
of several attributes.  If you have such a combination of
attributes, there is (from the relational model's point of
view) no need for a separate gid.
Also, if geometry is considered fist class data type of the
(extended) relational model, location itself can, in principle,
act as (or be a part of) a primary key (as long as you can
guarantee that there is only one object per location).

As pointed out in several other postings, the "problem" is that
a lot of GIS software tend to rely on having a single attribute
primary key, and that this key has to be of a specific type.

The system (PostgreSQL) generated "oid" can also be used as a
primary key.

Håvard

Marc Compte wrote:

> Hi,
>
> Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value)
> in a relational data base is guaranteed to be logically accessible by
> resorting to a combination of table name, primary key value and column
> name."?
>
> I find it surprising to see that "there's no need for a gid, from the
> database point of view" and that primary keys are only needed because
> "most applications want some way of identifying specific rows", as the
> very core definition of the relational model clearly identifies this as
> a crucial part of the model. Wanting to identify specific rows is not an
> application oddity, it's part of the very definition of the model.
>
> Every table needs to have a key, not only to conform with the relational
> model's theory, but for pragmatical purposes as well. Even (or
> specially) from the database point of view, no key means there is no way
> to update a unique field, there is no way to extract the information
> about one individual record, there is no way to cross-reference tables
> ... in short, no key means something like there is no relational
> database, only a flat list of data ... and PostgreSQL, even though it
> incorportes OO elements, has still a big part of relational, right?
>
> Whether you have to be using that particular field (gid) or not depends
> on your data. I don't think naming your key field "gid" is defined in
> any standard (it shouldn't be anyway). As other people said, shp2pgsql
> automatically generates this one for you, but I assume that is just to
> make your life easier and make sure there is at least one way to
> uniquely identify records.
>
> The only important thing to do should be to make sure that there's at
> least 1 field with values that can be used to uniquely identify any of
> the records of your table. If you are sure there's another field (or
> combination of fields) that serves the purpose right, you wouldn't
> actually need to have that particular gid field. You should be able to
> delete it and still be able to use on that table all of the features of
> the database and any other surrounding application by using your own
> self-made primary key.
>
> If an application has a problem with any of this (multiple field keys,
> using a different name for the key field other than gid, ...) then you
> could probably say it is not well designed and you might want to switch
> to something else.
>
> Marc
>
> En/na Markus Schaber ha escrit:
>> Hi, Antonello,
>>
>> Antonello <antonello.monetsen@...> wrote:
>>
>>  
>>> I have a dilemma: is gid really useful in a geoDB?
>>> I am building a geodb for several harbor. When I create a layer(table
>>> with geometry) in postgis, I need a gid like a primary key, or I can
>>> use a different one? Can I  use several field of from the table like
>>> a primary key?
>>> I am not new in the gis, but why in almost all geoDB I saw there is a
>>> gid?! Is it really necessary? Do it come from shapefile standards?
>>>    
>>
>> Technically, there's no need for a gid, from the database point of
>> view, as SQL deals with sets of rows.
>>
>> However, most applications want some way of identifying specific rows,
>> especially when updating / editing data.
>>
>> GID is generated by shp2pgsql.
>>
>>  
>>> For example QuantumGIS doesn't work if the layer has only one primary
>>> key, there are other application that don't work like qGis?
>>>    
>>
>> QuantumGIS has the additional oddity that the primary key has to be of
>> type int4 - int8 or serial (which is optimal for primary keys) is not
>> accepted, AFAIK.
>>
>> For read-only access, you can try MezoGIS which makes very little
>> assumptions about your database, and you can specify layers via
>> arbitrary queries.
>>
>>
>> Regards,
>> Markus
>>
>>  
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
Håvard Tveite
Department of Mathematical Sciences and Technology, UMB
Drøbakveien 14, POBox 5003, N-1432 Ås, NORWAY
Phone: [phone] Fax: [phone] http://www.umb.no/imt
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[pcreso]

--- Marc Compte <mcompte@...> wrote:

> Hi,
>
> Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value)
> in a relational data base is guaranteed to be logically accessible by
> resorting to a combination of table name, primary key value and column
> name."?

Yep.

> I find it surprising to see that "there's no need for a gid, from the
> database point of view" and that primary keys are only needed because
> "most applications want some way of identifying specific rows", as the
> very core definition of the relational model clearly identifies this as
> a crucial part of the model. Wanting to identify specific rows is not an
> application oddity, it's part of the very definition of the model.


I think you are confusing an automatically generated gid attribute with a
primary key. Sure, it could be a primary key, but there may well be others,
which may be data driven (single or composite).

Making the gid optional, simply allows the user to use a gid if appropriate, or
lets them use an alternative primary key. If the table already has a primary
key, there is indeed "no need for a gid".

Just like Postgres itself now has optional oids.

Making these fields optional does NOT make primary keys optional. (well, they
are, but the relational model is well & truly broken if you don't have one,
whether or not you actually define it as such. The data can have a viable
primary key, even if it is not specified as such in the schema)

Cheers,

  Brent Wood
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Mark Millman-3]

The problem is that the original SHAPE file doesn't have any attributes.  It
has attribution only by a one-to-one row correlation with an associated DBF
file.  Therefore the only way to associated the geometries found in the
SHAPE file with the non-spatial attribution found in the DBF file is by the
row number.  Blame ESRI and 30 years of history on this one.

The argument of using a automatic number or sequence as opposed to "natural"
data as a primary key is an old one and both sides of the argument have
validity.  Simple numbers index faster, join faster, and are found in almost
every database I've ever seen; however they are intrinsically meaningless.
But there are a lot of "John Smith"s in the world so we invent things like
Social Security Numbers and GIDs.

Historically GIS has supported many data sets that have no attribution,
other than their feature name; or they do have a unique "natural" attribute.
There are a lot of edges to the Mississippi River and I'd hate to have to
come up with a meaningful naming convention to keep them all unique.  One
might be tempted to use a distance criteria; until one remembers that rivers
move and the next elbow that is cut off would result in changes that would
be very difficult to manage.  So the only opportunity for a primary key is
the something like a GID.

So something like GID is important if you want to associate records that do
not have "natural" keys or if you want to avoid seriously long primary keys
like "USA,WA,Whatcom,Bellingham,Mill Ave,1340" for a primary key.

There is another argument for the use of a GID-like key and that is the
separation of spatial data from attribution, in a manner similar to that
used by some CAD based GIS like Bentley's Geographics and Intergraph's old
MGE.  These systems allowed for a single spatial instance to serve as a
topological boundary to many themes.  A single polyline could be the
boundary of a River, Municipality, County, and State; all at the same time.
Adjusting the line changed the all the associated boundaries simultaneously.
Geographics created topology dynamically based on the boundaries and
requested themes.  Themes had display priority so that the line was always
rendered appropriately for the senior most theme in the display list.

There are reasonable arguments in favor of such model in a purely database
solution too.  Each of the themes could have a foreign key to a common
spatial data table rather than embedding the spatial data directly in the
"theme" record.  This would avoid duplication of data, avoid double
plotting, and allow applications to manage shared boundaries consistently.

Mark Millman
Mizar, LLC
mark.millman@...
www.mizar.com
589 S Beach Rd.
Point Roberts, WA 98281
[phone]




-----Original Message-----
From: postgis-users-bounces@...
[mailto:postgis-users-bounces@...] On Behalf Of Havard
Tveite
Sent: Thursday, February 22, 2007 3:07 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] Dilemma: is gid really useful in a geoDB?

In the relational model, the primary key can be a combination
of several attributes.  If you have such a combination of
attributes, there is (from the relational model's point of
view) no need for a separate gid.
Also, if geometry is considered fist class data type of the
(extended) relational model, location itself can, in principle,
act as (or be a part of) a primary key (as long as you can
guarantee that there is only one object per location).

As pointed out in several other postings, the "problem" is that
a lot of GIS software tend to rely on having a single attribute
primary key, and that this key has to be of a specific type.

The system (PostgreSQL) generated "oid" can also be used as a
primary key.

Håvard

Marc Compte wrote:

> Hi,
>
> Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value)
> in a relational data base is guaranteed to be logically accessible by
> resorting to a combination of table name, primary key value and column
> name."?
>
> I find it surprising to see that "there's no need for a gid, from the
> database point of view" and that primary keys are only needed because
> "most applications want some way of identifying specific rows", as the
> very core definition of the relational model clearly identifies this as
> a crucial part of the model. Wanting to identify specific rows is not an
> application oddity, it's part of the very definition of the model.
>
> Every table needs to have a key, not only to conform with the relational
> model's theory, but for pragmatical purposes as well. Even (or
> specially) from the database point of view, no key means there is no way
> to update a unique field, there is no way to extract the information
> about one individual record, there is no way to cross-reference tables
> ... in short, no key means something like there is no relational
> database, only a flat list of data ... and PostgreSQL, even though it
> incorportes OO elements, has still a big part of relational, right?
>
> Whether you have to be using that particular field (gid) or not depends
> on your data. I don't think naming your key field "gid" is defined in
> any standard (it shouldn't be anyway). As other people said, shp2pgsql
> automatically generates this one for you, but I assume that is just to
> make your life easier and make sure there is at least one way to
> uniquely identify records.
>
> The only important thing to do should be to make sure that there's at
> least 1 field with values that can be used to uniquely identify any of
> the records of your table. If you are sure there's another field (or
> combination of fields) that serves the purpose right, you wouldn't
> actually need to have that particular gid field. You should be able to
> delete it and still be able to use on that table all of the features of
> the database and any other surrounding application by using your own
> self-made primary key.
>
> If an application has a problem with any of this (multiple field keys,
> using a different name for the key field other than gid, ...) then you
> could probably say it is not well designed and you might want to switch
> to something else.
>
> Marc
>
> En/na Markus Schaber ha escrit:
>> Hi, Antonello,
>>
>> Antonello <antonello.monetsen@...> wrote:
>>
>>  
>>> I have a dilemma: is gid really useful in a geoDB?
>>> I am building a geodb for several harbor. When I create a layer(table
>>> with geometry) in postgis, I need a gid like a primary key, or I can
>>> use a different one? Can I  use several field of from the table like
>>> a primary key?
>>> I am not new in the gis, but why in almost all geoDB I saw there is a
>>> gid?! Is it really necessary? Do it come from shapefile standards?
>>>    
>>
>> Technically, there's no need for a gid, from the database point of
>> view, as SQL deals with sets of rows.
>>
>> However, most applications want some way of identifying specific rows,
>> especially when updating / editing data.
>>
>> GID is generated by shp2pgsql.
>>
>>  
>>> For example QuantumGIS doesn't work if the layer has only one primary
>>> key, there are other application that don't work like qGis?
>>>    
>>
>> QuantumGIS has the additional oddity that the primary key has to be of
>> type int4 - int8 or serial (which is optimal for primary keys) is not
>> accepted, AFAIK.
>>
>> For read-only access, you can try MezoGIS which makes very little
>> assumptions about your database, and you can specify layers via
>> arbitrary queries.
>>
>>
>> Regards,
>> Markus
>>
>>  
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@...
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>

--
Håvard Tveite
Department of Mathematical Sciences and Technology, UMB
Drøbakveien 14, POBox 5003, N-1432 Ås, NORWAY
Phone: [phone] Fax: [phone] http://www.umb.no/imt
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Antonello Moneta]

Thanks a lot to all of you!

This discussion revolve all my doubt about the use of gid, Thanks! I
start the modeling of my database with the purpose to build a standards
geoDB which I could use with several free, and eventually, no-free
application. So I was scared that maybe gid was a standards for identify
the feature in a layer. But after yours help I have a clear idea about it.

Thank you a lot, bye Antonello.

Mark Millman wrote:

> The problem is that the original SHAPE file doesn't have any attributes.  It
> has attribution only by a one-to-one row correlation with an associated DBF
> file.  Therefore the only way to associated the geometries found in the
> SHAPE file with the non-spatial attribution found in the DBF file is by the
> row number.  Blame ESRI and 30 years of history on this one.
>
> The argument of using a automatic number or sequence as opposed to "natural"
> data as a primary key is an old one and both sides of the argument have
> validity.  Simple numbers index faster, join faster, and are found in almost
> every database I've ever seen; however they are intrinsically meaningless.
> But there are a lot of "John Smith"s in the world so we invent things like
> Social Security Numbers and GIDs.
>
> Historically GIS has supported many data sets that have no attribution,
> other than their feature name; or they do have a unique "natural" attribute.
> There are a lot of edges to the Mississippi River and I'd hate to have to
> come up with a meaningful naming convention to keep them all unique.  One
> might be tempted to use a distance criteria; until one remembers that rivers
> move and the next elbow that is cut off would result in changes that would
> be very difficult to manage.  So the only opportunity for a primary key is
> the something like a GID.
>
> So something like GID is important if you want to associate records that do
> not have "natural" keys or if you want to avoid seriously long primary keys
> like "USA,WA,Whatcom,Bellingham,Mill Ave,1340" for a primary key.
>
> There is another argument for the use of a GID-like key and that is the
> separation of spatial data from attribution, in a manner similar to that
> used by some CAD based GIS like Bentley's Geographics and Intergraph's old
> MGE.  These systems allowed for a single spatial instance to serve as a
> topological boundary to many themes.  A single polyline could be the
> boundary of a River, Municipality, County, and State; all at the same time.
> Adjusting the line changed the all the associated boundaries simultaneously.
> Geographics created topology dynamically based on the boundaries and
> requested themes.  Themes had display priority so that the line was always
> rendered appropriately for the senior most theme in the display list.
>
> There are reasonable arguments in favor of such model in a purely database
> solution too.  Each of the themes could have a foreign key to a common
> spatial data table rather than embedding the spatial data directly in the
> "theme" record.  This would avoid duplication of data, avoid double
> plotting, and allow applications to manage shared boundaries consistently.
>
> Mark Millman
> Mizar, LLC
> mark.millman@...
> www.mizar.com
> 589 S Beach Rd.
> Point Roberts, WA 98281
> [phone]
>
>
>
>
> -----Original Message-----
> From: postgis-users-bounces@...
> [mailto:postgis-users-bounces@...] On Behalf Of Havard
> Tveite
> Sent: Thursday, February 22, 2007 3:07 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Dilemma: is gid really useful in a geoDB?
>
> In the relational model, the primary key can be a combination
> of several attributes.  If you have such a combination of
> attributes, there is (from the relational model's point of
> view) no need for a separate gid.
> Also, if geometry is considered fist class data type of the
> (extended) relational model, location itself can, in principle,
> act as (or be a part of) a primary key (as long as you can
> guarantee that there is only one object per location).
>
> As pointed out in several other postings, the "problem" is that
> a lot of GIS software tend to rely on having a single attribute
> primary key, and that this key has to be of a specific type.
>
> The system (PostgreSQL) generated "oid" can also be used as a
> primary key.
>
> Håvard
>
> Marc Compte wrote:
>  
>> Hi,
>>
>> Ermm ... wasn't one of Codd's rules "Each and every datum (atomic value)
>> in a relational data base is guaranteed to be logically accessible by
>> resorting to a combination of table name, primary key value and column
>> name."?
>>
>> I find it surprising to see that "there's no need for a gid, from the
>> database point of view" and that primary keys are only needed because
>> "most applications want some way of identifying specific rows", as the
>> very core definition of the relational model clearly identifies this as
>> a crucial part of the model. Wanting to identify specific rows is not an
>> application oddity, it's part of the very definition of the model.
>>
>> Every table needs to have a key, not only to conform with the relational
>> model's theory, but for pragmatical purposes as well. Even (or
>> specially) from the database point of view, no key means there is no way
>> to update a unique field, there is no way to extract the information
>> about one individual record, there is no way to cross-reference tables
>> ... in short, no key means something like there is no relational
>> database, only a flat list of data ... and PostgreSQL, even though it
>> incorportes OO elements, has still a big part of relational, right?
>>
>> Whether you have to be using that particular field (gid) or not depends
>> on your data. I don't think naming your key field "gid" is defined in
>> any standard (it shouldn't be anyway). As other people said, shp2pgsql
>> automatically generates this one for you, but I assume that is just to
>> make your life easier and make sure there is at least one way to
>> uniquely identify records.
>>
>> The only important thing to do should be to make sure that there's at
>> least 1 field with values that can be used to uniquely identify any of
>> the records of your table. If you are sure there's another field (or
>> combination of fields) that serves the purpose right, you wouldn't
>> actually need to have that particular gid field. You should be able to
>> delete it and still be able to use on that table all of the features of
>> the database and any other surrounding application by using your own
>> self-made primary key.
>>
>> If an application has a problem with any of this (multiple field keys,
>> using a different name for the key field other than gid, ...) then you
>> could probably say it is not well designed and you might want to switch
>> to something else.
>>
>> Marc
>>
>> En/na Markus Schaber ha escrit:
>>    
>>> Hi, Antonello,
>>>
>>> Antonello <antonello.monetsen@...> wrote:
>>>
>>>  
>>>      
>>>> I have a dilemma: is gid really useful in a geoDB?
>>>> I am building a geodb for several harbor. When I create a layer(table
>>>> with geometry) in postgis, I need a gid like a primary key, or I can
>>>> use a different one? Can I  use several field of from the table like
>>>> a primary key?
>>>> I am not new in the gis, but why in almost all geoDB I saw there is a
>>>> gid?! Is it really necessary? Do it come from shapefile standards?
>>>>    
>>>>        
>>> Technically, there's no need for a gid, from the database point of
>>> view, as SQL deals with sets of rows.
>>>
>>> However, most applications want some way of identifying specific rows,
>>> especially when updating / editing data.
>>>
>>> GID is generated by shp2pgsql.
>>>
>>>  
>>>      
>>>> For example QuantumGIS doesn't work if the layer has only one primary
>>>> key, there are other application that don't work like qGis?
>>>>    
>>>>        
>>> QuantumGIS has the additional oddity that the primary key has to be of
>>> type int4 - int8 or serial (which is optimal for primary keys) is not
>>> accepted, AFAIK.
>>>
>>> For read-only access, you can try MezoGIS which makes very little
>>> assumptions about your database, and you can specify layers via
>>> arbitrary queries.
>>>
>>>
>>> Regards,
>>> Markus
>>>
>>>  
>>>      
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users@...
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>    
>
>  

_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Markus Schaber-7]

Hi, Marc,

Marc Compte <mcompte@...> wrote:

> I find it surprising to see that "there's no need for a gid, from the
> database point of view" and that primary keys are only needed because
> "most applications want some way of identifying specific rows",

You misinterpreted my statement.

As you wrote yourself, the means of identifying a row may be an
arbitrary combination of columns. It might even be all columns
together. For some applications, even the geometry itsself might
serve as the primary key.

And the relational model is designed to work on sets of rows, the
primary key is simply a means of constraining such a set to a single
row. Whether and where you need that depends on your application and
data model.

So, e. G., for a simple map painting application, you use queries that
fetch all data from a specific area (via && operator, and GIST index),
possibly filtered by some criteria (route_type to only get motorways
when zoomed out), no need for primary keys in that case.

> Every table needs to have a key, not only to conform with the relational
> model's theory, but for pragmatical purposes as well. Even (or
> specially) from the database point of view, no key means there is no way
> to update a unique field, there is no way to extract the information
> about one individual record, there is no way to cross-reference tables
> ... in short, no key means something like there is no relational
> database, only a flat list of data ...

And in some of our applications, we just need such flat lists of data.
There are no updates, only additions, and the data is processed
bunch-wise (via time-range queries, geometrical area queries, etc. and
possibly aggregates).

Those applications are not the typical OLPC ones.

But it stays correct that the database itsself does not need any keys
per se to work, its a requirement of specific applications and data
models. And, of course, it's important that a database provides ways to
enforce uniqueness and referential integrity. (Hello, MySQL? :-)

But what I really wanted to complain about is the braindeadness that
some GIS viewers blindly assume that there is a primary key column named
"gid" and of type "int4", with no way to convince them that:

- there's a unique "rowid" of type "int8" they can use
- the unique indexed tuple (gid int4, area_id int4) is a pk they can
  use, as "gid" is only unique per shapefile (= area).
- they should not segfault when "gid" is of type "int8"
- for pure viewing purposes, no PK is needed at all.

Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Paolo Corti]

exactly, adding the gid column is a convenience
in some case is mandatory
zigGis, the OS PostGIS connector for ArcGis, is for example waiting to always have the gid field defined, otherwise it won't work.
Some other software can work even without it (QGis, but still needs a primary key), but in the case of Esri GDB model there is a mandatory OID integer field that will be translated from the gid field of a PostGIS layer, and there is no way to avoid to add the gid field in PostGIS tables.
At this time zigGIs is assuming that the OID field is named gid, but maybe we can reconsider the thing, and make it this name configurable in configuration file....

Paolo Corti

Paul Ramsey wrote:

Correct, almost all client applications require a primary key.  Many
require that the primary key be a single column.  shp2pgsql creates a
'gid' automatically, but you could always drop it if you wanted some
other primary key.

Stephen Woodbridge wrote:
> Antonello wrote:
>> Hi list,
>>
>> I have a dilemma: is gid really useful in a geoDB?
>> I am building a geodb for several harbor. When I create a layer(table
>> with geometry) in postgis, I need a gid like a primary key, or I can
>> use a different one? Can I  use several field of from the table like a
>> primary key?
>>
>> I am not new in the gis, but why in almost all geoDB I saw there is a
>> gid?! Is it really necessary? Do it come from shapefile standards?
>>
>> For example QuantumGIS doesn't work if the layer has only one primary
>> key, there are other application that don't work like qGis?
>>
>
> Hello Antonello,
>
> There is basically a requirement to have some kind of unique way of
> accessing any given record. This is so programs can update the record or
> fetch the records later based on a unique id. Also some systems might
> expect that the uid be an integer. So adding the gid column is a
> convenience.
>
> If you know your usage patterns and you know the requirements for the
> software you are using with the database, then you can probably safely
> remove or change it to something else. I don't think gid needs to be the
> primary key if you have something else, you could just make the gid a
> unique column.
>
> -Steve
> _______________________________________________
> postgis-users mailing list
> [e-mail]
> http://postgis.refractions.net/mailman/listinfo/postgis-users


--

   Paul Ramsey
   Refractions Research
   http://www.refractions.net
   [e-mail]
   Phone: [phone]
   Cell: 250-885-0632
_______________________________________________
postgis-users mailing list
[e-mail]
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Sean Fulton]

On 2007-02-22 13:05:29 -0500, Brent Wood <pcreso@...> said:
>
> Making these fields optional does NOT make primary keys optional. (well,
> they are, but the relational model is well & truly broken if you don't
> have one,
> whether or not you actually define it as such. The data can have a viable
> primary key, even if it is not specified as such in the schema)

Strictly speaking, the relational model is broken when there are
non-unique tuples. When all tuples are unique, you have a de facto
primary key -- the whole tuple. Uniqueness is, I think, the only
requirement. A primary key is a convenience that summarizes the
uniqueness of a tuple. In the fourth normal form and higher they are
unnecessary.

        Sean


_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users

[Brad Ediger]

On Mar 23, 2007, at 1:03 PM, Sean Fulton wrote:

> On 2007-02-22 13:05:29 -0500, Brent Wood <pcreso@...> said:
>> Making these fields optional does NOT make primary keys optional.  
>> (well,
>> they are, but the relational model is well & truly broken if you  
>> don't have one,
>> whether or not you actually define it as such. The data can have a  
>> viable
>> primary key, even if it is not specified as such in the schema)
>
> Strictly speaking, the relational model is broken when there are  
> non-unique tuples. When all tuples are unique, you have a de facto  
> primary key -- the whole tuple. Uniqueness is, I think, the only  
> requirement. A primary key is a convenience that summarizes the  
> uniqueness of a tuple. In the fourth normal form and higher they  
> are unnecessary.

If you want to get pedantic, the whole tuple would be a candidate  
key. A candidate key is only a primary key when it is singled out as  
such.

Back to our regularly scheduled programming...

be
_______________________________________________
postgis-users mailing list
postgis-users@...
http://postgis.refractions.net/mailman/listinfo/postgis-users