Issue: we have 21 Million records in a single table in a PostGreSQL/PostGIS database.
column attributes are mostly time/date stamps, simple text fields (type) and one geometry field (bounding box/ bbox).
We want users to search/query from a web page running Telerik Kendo (a jQUERY User Interface and AJAX interface (AJAX/JQUERY) . At the server the search will look like REST GET requests. The key part of the REST request is a parameter called bbox which is used as a filter to restrict the query to small area. Timestamp ranges will also be part of the search filter.
On the server side, a WCF data service will receive the AJAX request, convert it into SQL SELECT request (we need to use WHERE clauses with the ST_INTERSECT() spatial functions to optimize performance). We are planning of using DevArt DotConnect for PostGreSQL for the .NET database API. We are not clear if an ORM really will help us, or hurt our performance.
The result records (18 fields of meta data fields per row) will be returned via Microsoft WCF ODATA (in JSON format) to the JQUERY and then data mapped to both GRID and Chart displays.
Data might have to be paged via ODATA paging services on the server (or should we do this on the client, since the return sets should be fairly small. on the order of 10 to 1000).
1. Does anything of what I said sound stupid, based on best practices (I cannot change the choice of PostGIS or KENDO) and since the server is a windows 7 machine and we are VisualStudio C# programmers WCF sounds like a better way that JAVA, Glassfish, Apache, etc.
2. What we really need is samples of any of the steps that I outlined that we can use as design ideas for what we are doing.
3. Any caveats and gotchas we should be concerned about in doing this project with these tools?