MapServer, PostGIS, Subquery with JOIN, WMS GetFeatureInfo / Followup Question

[Paul Ramsey]

If you have the option, please don't use oid, use a primary key (like  
the 'gid' created by shp2pgsql) as your unique key.  Primary keys  
already have indexes, oids do not.  Primary keys show up  
automatically in a "select *" query, oids do not.  oids are  
deprecated in pgsql and not available by default in pgsql 8.1.   
Basically oid is now a deadend, and we need to start erasing all uses  
of them.

P

On 24-Feb-06, at 5:09 AM, Jeff Hoffmann wrote:

> Nick Floersch wrote:
>>   
>> --------------------------------------------------------------------- 
>> ---
>> *From:* Nick Floersch
>> *Sent:* Friday, February 24, 2006 5:03 AM
>> *To:* Nick Floersch
>> *Subject:* RE: MapServer, PostGIS, Subquery with JOIN, WMS  
>> GetFeatureInfo / Followup Question
>> After my just previous post to the list, I decided to try my  
>> alternative solution...
>>   "" Before anyone suggests the solution of using a view to  
>> encapsulate my JOIN so it need not be in the subquery, I'll just  
>> say that I am aware I could try that approach (though I have not  
>> yet done so), but my curiosity has been piqued by this problem as  
>> it is, so I'm hoping to find out more about the JOINs in  
>> subqueries issue I have. I would also really love to know how  
>> MapServer gets the attribute columns for PostGIS layers. ""
>
> This is on my "figure out some day myself" list, too.  I'm doing  
> the view thing right now, but I'd like to not have to create views  
> for everything.
>
>> ---snip---
>>  The only thing I can think of is... does the PostGIS connector  
>> require the table to have OIDs? It looks that way.
>
> Yes.  It needs some unique field in order to randomly access an  
> individual rows, it just so happens that OID is a convenient way to  
> get that in most cases.  You can also specify your own unique  
> column name with "using unique <column name>" if your view doesn't  
> have an OID column but you have some other key you can use.  I just  
> pull in the OID from the main geometry-containing table when I  
> define the view.
>
> --
> Jeff Hoffmann
> jeff at propertykey.com