4

I am importing thousands of .asc files into PostGIS into this table:

CREATE TABLE stg.COUNTRY_1M(rid serial primary key, rast raster);
CREATE INDEX ON stg.COUNTRY_1M USING gist (st_convexhull(rast));

from command line:

export PGPASSWORD=mypasswd
for i in $(ls *.asc); do raster2pgsql -a -C -I -s 27700 -t 1000x1000 "$i" stg.COUNTRY_1M | psql -U user1 -d data_processing -h localhost -q; done

but I am getting the following error starting from the second file:

Processing 1/1: country_1m.asc
ERROR:  new row for relation "country_1m" violates check constraint "enforce_max_extent_rast"
DETAIL:  Failing row contains (2, 0100000100000000000000F03F000000000000F0BF0000000080841E41000000...).
etc.

How can I generate one table for all the raster points without errors?

Improve this question
5
  • 1
    This happens because the -C gets applied to each asc file, as you are running raster2pgsql multiple times. So, either add the constraint afterwards, or ignore it. Personally, I don't think it is of any use whatsover, as when you are subsetting tiles on a known grid, they are going to be in the right place anyway. By the way, if this is UK Lidar, depending on what kinds of queries you might want to run on these tables, you are likely to find that 1000 x 1000 is way too big. I usually go for 100-200 range, as I do lots of small vector/raster overlay queries.
    – John Powell
    Commented May 4, 2018 at 10:48
  • 1
    Also, the -I switch will index each tile as you go, so not sure you need to create the index before doing the insert. The -C is only useful, imho, if you are likely to insert into this database to prevent going outside the tile boundary. If it is essentially a write-once, read-often table, then there is little point.
    – John Powell
    Commented May 4, 2018 at 10:56
  • @JohnPowellakaBarça thank you for your suggestions. I am running the big import. It is the first time I use this dataset. The asc files are 1000x1000 (as number of data each). If I restrict that to 100x100 does it mean that every single record represents actually 10 points?
    – Randomize
    Commented May 4, 2018 at 12:14
  • BTW if I use -I it will keep on add indexes. So I will just create a final index at the end.
    – Randomize
    Commented May 4, 2018 at 12:18
  • 1
    The comment was getting a bit long, so I put it all as an answer. There is a much cleaner way, avoiding the for , do, done loop and using *.asc instead. Also, some comments on t.
    – John Powell
    Commented May 4, 2018 at 12:58

1 Answer 1

Reset to default
6

You can do all of this in one step, eg,

raster2pgsql -c -s 27700 -C -I -f rast *.asc -t 100x100 stg.COUNTRY_1M 
| psql -U treex -d data_processing -h localhost

-c means create, which is the default. Because you can use *.asc, there is no need to use

for i in $(ls *.asc); do raster2pgslq ...; done

type logic, as all .asc files will be passed to raster2pgsql, and this will avoid the error you are seeing of trying to add tiles to a table that already has constraints on.

As an aside, from your previous question, I know that this is UK LIDAR, which is quite a large dataset. You would be well advised to use -t switch to tile you input, where tile size is expressed in pixels, eg, -t 100x100. Something in the range of 100-200 pixels seems to work well.

There is not that much literature on this, though this blog outlines some of the effects of tile size on performance.

As you can see from the image below, polygon/raster overlay functions get much faster with smaller tile sizes, which makes sense, to a point. I was doing lots of vector building/LIDAR tile queries, and found tiling led to an order of magnitude improvement in query time. Obviously, this will depend somewhat on the size of your vectors.

enter image description here

Improve this answer
4
  • Really thank you! Have you an idea how much the whole imported dataset will take as db storage space?
    – Randomize
    Commented May 4, 2018 at 13:04
  • Smaller than the asc files, for sure. You can always run a few to get an idea of the ratio of asc to raster (plus index size) to see if you will have enough space. Please accept the answer if it has helped you.
    – John Powell
    Commented May 4, 2018 at 13:09
  • This is great analysis-- note that polygon and point Overlay/Intersects performance can be very different.
    – ak112358
    Commented Feb 14, 2020 at 15:18
  • Yes, indeed, I should have made that more explicit. There is going to be a very different peformance implication if your polygons are very large and cover many small tiles, whereas points are a bit easier to predict.
    – John Powell
    Commented Feb 14, 2020 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.