[postgis-users] pgsql2shp - question/problem

Stephen Woodbridge woodbri at swoodbridge.com
Mon Oct 31 10:55:35 PDT 2016

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 

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.


This email has been checked for viruses by Avast antivirus software.

More information about the postgis-users mailing list