5

I am creating shapefile using shapefile.py and my database has records more that 65k which is the limit of the shapefile. How can I create shapefiles then?

Is it possible that somehow I am able to creat shapefile and store them directly into the PostGIS using shapefile.py?

underdark
  • 84,148
  • 21
  • 231
  • 413
Sam007
  • 4,395
  • 7
  • 48
  • 69

1 Answers1

11

Theoretically, a shapefile can contain about 70 million point features. The size of either the .shp or .dbf files must not exceed 2GB.

Also, why bother creating a shapefile to insert to PostGIS? Try directly inserting the data using something like psycopg2, which makes the whole import process simpler, faster, and more direct.

Here are a few short examples to directly insert to PostGIS. First with simple point data, then with more complicated polygon data (a similar geometry construction and WKB export can also be done with OGR).

import psycopg2
from random import random
from shapely.geometry import Polygon

Make a connection https://www.psycopg.org/docs/module.html


conn = psycopg2.connect(dbname='mydb')
curs = conn.cursor()


Insert Point data into mypts


curs.execute('''

    create table if not exists mypts(gid serial primary key, geom geometry(Point,4326));
''')


Some longitude/latitude points (in that order!)


lon_lat = [(random(), random()) for x in range(100)]
curs.executemany('''

    insert into mypts(geom) values(ST_SetSRID(ST_MakePoint(%s, %s), 4326));
''', lon_lat)
conn.commit()


Insert Polygon data into mypoly


curs.execute('create table if not exists mypoly(gid serial primary key, geom geometry(Polygon,4326))')


Use Shapely to piece together exterior linear ring, and interior rings with lists of coordinates


poly = Polygon(
            shell=[(0, 0), (0, 10), (10, 10), (10, 0), (0, 0)],
            holes=[[(2, 2), (2, 6), (7, 7), (2, 2)]])
curs.execute('''

    insert into mypoly(geom) values(ST_SetSRID(%s::geometry, 4326));
''', (poly.wkb_hex,))
conn.commit()
Mike T
  • 42,095
  • 10
  • 126
  • 187
  • 3
    +1 for don't bother with Shapefiles if you can have PostGIS. – underdark Jul 07 '12 at 11:15
  • Thanks for the reply @Mike, but the problem is like this, I already have the attribute data in MySql and now we are planning to move to Postgres and use PostGIS. So my script which uses shapefile.py is extracting info from the raster and lat/lon from MySql db and then creating shapefile. So I need to add this directly to PostGIS and then view it using Geoserver. So how can I do that? – Sam007 Jul 16 '12 at 16:25
  • @Sam007 not sure what you are asking exactly; you might need to ask a separate question with much more detail. Generally, try ogr2ogr to transfer spatial data between databases. If that is insufficient, there are many different ways to build a python script to directly insert data. – Mike T Jul 16 '12 at 18:46
  • would u know of any sample code for adding the data by code to PostGIS database directly? – Sam007 Jul 16 '12 at 19:28
  • 1
    @Sam007 there are several methods, but it depends on the type of geometries. For instance, inserting point data is simpler than inserting polygon data. The former I'd use ST_MakePoint, while the later, I'd construct the linear rings with OGR or Shapely, then generate the WKB to insert. – Mike T Jul 16 '12 at 19:38