GeoSpatial Queries

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for PostgreSQL
Post Reply
Posts: 28
Joined: Fri 09 Mar 2012 15:10

GeoSpatial Queries

Post by drysg » Mon 12 Mar 2012 16:35

We have a 21 million table in PostGIS database. The fields are all simple text except for one geometry field (a simple Polygon rectangle which is a bounding box.

We typically do just read queries of the form:

Code: Select all

FROM portal.metadata as cat
WHERE ST_Intersects(ST_GeomFromText('POLYGON((-70 42,-71 42,-71 41,-70 41,-70 42))', 4326), cat.location);
Now that we bought DotConnect Pro, I want to do this query from C# (an WCF application).

I am a newbie with DotConnect, I have read your blog post: ... ework.html

But it seems to me that this is not what we are looking for. First, the users are saying that the code is out of date. Secondly, it seems that there is NO reason to try to do the spatial intersect in LINQ.

Would it not make more sense to pass in the query rectangle to a stored procedure in the PostGreSQL DB, and then just return the text columns? We can then do additional LINQ style filtering.

But the fact that we have 21 million records (GIST indexed) means that for performance reasons we want to use the native geospatial power of PostGIS and not sharpMap or other spatial libraries .

Can anyone point me to examples of trying to expose stored procedures like the above to dotConnect?
Last edited by drysg on Mon 12 Mar 2012 16:42, edited 1 time in total.

Posts: 28
Joined: Fri 09 Mar 2012 15:10

Post by drysg » Mon 12 Mar 2012 16:42

I thought I would also add the DDL, since we plan to eventualy add queries that not only filter by BBox, but also by time ranges and type, and other fields.

Code: Select all

        uniq_id CHARACTER(64) NOT NULL,
        size_bytes BIGINT,
        type CHARACTER VARYING(25),
        classification CHARACTER VARYING(7),
        distributor CHARACTER VARYING(50),
        egpl_date TIMESTAMP(6) WITHOUT TIME ZONE,
        classification_int INTEGER,
        handling CHARACTER VARYING(50),
        creator CHARACTER VARYING(40),
        description CHARACTER VARYING(80),
        datum CHARACTER VARYING(20),
        do_location CHARACTER VARYING(200),
        elevation_ft INTEGER,
        location USER-DEFINED,
        idx BIGINT NOT NULL,
        bbox CHARACTER VARYING(160),
        product CHARACTER VARYING(20),
        CONSTRAINT catalog_key PRIMARY KEY (idx),
        CONSTRAINT enforce_srid_location CHECK (srid(location) = 4326),
        CONSTRAINT enforce_dims_location CHECK (ndims(location) = 2),
        CONSTRAINT enforce_geotype_location CHECK ((geometrytype(location) = 'POLYGON'::text)
    OR  (
            location IS NULL))

Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 16 Mar 2012 17:22

We have answered you by e-mail.

Post Reply