空间数据的插入与查询(Django+Postgis)

建表语句

CREATE TABLE soildata
(
  sid character varying(32) NOT NULL,
  stype character varying(32),
  pos geometry,
  CONSTRAINT soildata_pkey PRIMARY KEY (sid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE soildata
  OWNER TO postgres;

Django模型

from django.contrib.gis.db import models
from django.contrib.gis.db.models.manager import GeoManager

# Create your models here.

class SoilData(models.Model):
    sid = models.CharField(max_length=32, primary_key=True)
    stype = models.CharField(max_length=32)
    pos = models.GeometryField(srid=4326)
    
    objects = GeoManager()
    
    class Meta:
        db_table = 'soildata'

插入空间数据

方式1：原生SQL语句

#encoding:utf-8
import psycopg2

if __name__ == '__main__':
    conn = psycopg2.connect(database="gisdb",user="postgres",password="",host="127.0.0.1",port="5432")
    cursor = conn.cursor()
    
    cursor.execute(
        'insert into soildata values(%s, %s, %s);',
        ("1","棕壤","SRID=4326;POINT(120 38)")
    )
    
    cursor.close()
    conn.commit()
    conn.close()

方式2：Django model

soil = SoilData()
soil.sid = "2"
soil.stype = "红壤"
pos = GEOSGeometry('POINT(%s %s)'%(100,30),4326)
soil.pos = pos
soil.save()

空间关系查询

>>> from django.contrib.gis.geos.geometry import GEOSGeometry
>>> from myapp.models import SoilData
>>> wheres = ['ST_Contains(ST_GeomFromText(%s, 4326), pos)']
>>> wheres_param = ['POLYGON((99 29,99 31,100 29,100 31,99 29))']
>>> dao = SoilData.objects.extra(where=wheres, params=wheres_param)
>>> dao.count()
0
>>> wheres_param = ['POLYGON((99 20,99 31,101 31,101 20,99 20))']
>>> dao = SoilData.objects.extra(where=wheres, params=wheres_param)
>>> dao.count()
1
>>> wheres_param = ['POLYGON((0 0,0 90,180 90,180 0,0 0))']
>>> dao = SoilData.objects.extra(where=wheres, params=wheres_param)
>>> dao.count()
2