[postgis-users] pgsql2shp - question/problem

[Stephen Woodbridge]

On 10/31/2016 11:59 AM, Stephen Woodbridge wrote:
> Hi all,
>
> I'm trying to reorder a shape file based on an attribute column using a
> sql query with an order by clause, but the resulting file always seems
> to be ordered by gid. Is pgsql2shp wrapping the sql query and re
> ordering it? Why?
>
> shp2pgsql -s 4283 -d -D -N skip data/topo-2m/localities/aus25lgd_p.shp
> rawdata.aus25lgd_p | psql -U postgres -h localhost -p 5435 mydb
>
> pgsql2shp -u postgres -h localhost -p 5435 -f a.shp mydb "select * from
> rawdata.aus25lgd_p order by population::integer desc"
>
> $ dbfdump a | head
> 0:0:1:1:locality:GARY JUNCTION:5:0:BI000001:DJ00015139
> 0:0:2:2:locality:EVERARD JUNCTION:5:0:BI000001:DJ00015140
> 0:0:3:3:locality:NEALE JUNCTION:5:0:BI000001:DJ00015141
> 0:0:4:4:locality:POINT JAHLEEL:3:0:BJ000004:DJ00015142
> 0:0:5:5:locality:CAPE FLEEMING:3:0:BJ000004:DJ00015143
> 0:0:6:6:locality:SMOKY POINT:3:0:BJ000004:DJ00015144
> 0:0:7:7:locality:PURUMPENELLI POINT:3:0:BJ000004:DJ00015145
> 0:0:8:8:locality:RADFORD POINT:3:0:BJ000004:DJ00015146
> 0:0:9:9:locality:SOLDIER POINT:3:0:BJ000004:DJ00015147
> 0:0:10:10:locality:CAPE KEITH:3:0:BJ000004:DJ00015148
>
> This is the order of the original shapefile.
>
> I also tried to use mapserver's sortshp but the POPULATION field is
> character varying(7) so it does not do a numeric sort.
>
> I'll give ogr2ogr a try, but it seems like if I give a sql query to
> pgsql2shp that it should not change it.
>

For the record, I was able to work around this problem by creating a new 
table from the imported one and dropping the gid column, like:

create table rawdata.aus25lgd_p2 as select * from rawdata.aus25lgd_p 
order by population::integer desc;
alter table rawdata.aus25lgd_p2 drop column gid;

# then export it as raw, like:
pgsql2shp -u postgres -h localhost -p 5435 -f a.shp -r mydb 
rawdata.aus25lgd_p2

Maybe this will help someone else that runs into this problem, but I 
still think pgsql2shp should not reorder the query results or at least 
provide the option to not reorder them.

-Steve

---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus