GeoSpatial Queries

[drysg]

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

SELECT * 
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:
http://www.devart.com/blogs/dotconnect/ ... 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?

[drysg]

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

CREATE TABLE
    catalog
    (
        uniq_id CHARACTER(64) NOT NULL,
        size_bytes BIGINT,
        DATE TIMESTAMP(6) WITHOUT TIME ZONE,
        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))
    )

[Shalex]

We have answered you by e-mail.