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?
> 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.