How to do spatial query and sort by column values at the same time?

ThinkGeo UI for Desktop WinForms
1

Hi,


I found all the spatial queries check the geometry only, all the SQL queries return a datatable only whithout the geometries.


How to do a spatial query and sql query at the same time, for example on a ShapeFileFeatureLayer?


For example, select * from TableName where TableName.obj intersects Geometry1 and TableName.col1=*** order by TableName.Col2


So the returned result contains all the column values along with the geometry object and in a sorted order?


Thanks


Rose



2

Rose, 
  
 For ShapeFileFeatureLayer, I think we have no way to implement this up to now, while in MsSqlFeatureLayer or PostgreFeatureLayer, we probablly can achive this by using the event ExecutingSqlStatement. 
  
 For the ShapeFileFeatureLayer, I think the SpatialQuery should be very fast based on RTree used, so what I suggest is first to the SpatialQuery and then do the SqlQuery based on the SpatialQuery result. 
  
 Sorry for the inconvinience now. 
  
 Thanks. 
  
 Yale 


3

Yale:


Thanks.


For the PostgreFeatureLayer, the QueryTools.GetFeaturesIntersecting() only do spatial query, how to make it to do sql query at the same time?  For example the returned collection of Features are sorted by column1, column2...


Thanks


Rose



4

Rose, you are welcome.


To achieve this, we have to change the sqlstatement in the following event:

((PostgreSqlFeatureSource)(postgreLayer.FeatureSource)).ExecutingSqlStatement += new EventHandler<ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs>(DisplayShapeMap_ExecutingSqlStatement);
 
void DisplayShapeMap_ExecutingSqlStatement(object sender, ExecutingSqlStatementPostgreSqlFeatureSourceEventArgs e)
{
   if (e.ExcutingSqlStatementType == ExecutingSqlStatementType.GetFeaturesInsideBoundingBoxEx)
   {
      e.SqlStatement = e.SqlStatement.Remove(e.SqlStatement.Length - 1) + " ORDER BY COLUMN1 DESC;";
   }
}

 
Any more questions just feel free to let me know.
 
Thanks.
 
Yale

5

Yale:


I checked the SqlStatement in the ExecutingSqlStatement event, the geometry column is retrieved twice,


The geometry column is called "ais_geom",  See the statement below:


Query1=select "aispolylines_id","startdate","enddate",AsBinary("ais_geom") as "ais_geom",AsBinary("ais_geom") as "ais_geom" from "tracksview" where GeomFromWKB(:geometry,4326) && "ais_geom";


Is this a bug?


Rose


 



6

Rose, 
  
 Thanks for your post and questions. 
  
 Just want to know what are the ExcutingSqlStatementType for the twice events?  That maybe be reasonably because we did not cache its filed , we will retrieve it everytime we need it. 
  
 Any more questions just feel free to let me know. 
  
 Thanks. 
  
 Yale 


7

Yale:

 




The ExecutingSqlStatementType is GetFeaturesInsideBoundingBoxEx. You overlooked my question, I did not say the event fired twice, I said the geometry column was retrieved twice in the same query.



Rose



8

Rose,


Sorry for making this mistake.
 
I tested with our very simple sample, it works fine, it will only regrieve the geometry column once, could you explain what special operation for you case?
 
select AsBinary("shape") as "shape",recid from "usstates" where GeomFromWKB(:geometry,4326) && "shape" ORDER BY COLUMN1 DESC;
 
Any more questions just feel free to let me know.
 
Thanks.
 
Yale

9

Rose, Yale, 
  
 I did not test it, but it sounds related to an issue that I discovered. When there are multiple tables/views present in the database having the same name, the union of the column names of all the tables/views with the same name will be retrieved, and eventually duplicated multiple times. It could be that also the geometry column will be retrieved twice, or even more often. The issue was solved, and the API now includes an extra overload when constucting the PostgreSqlFeatureLayer, one that includes the schema name. Note that also the spatial reference ID will not be read from the geometry_columns table anymore. You have to look it up yourself. This may look annoying, but in fact, it is a powerful ‘feature’, as it enables you to use temporary views, work with stored procedures and maybe even temporary tables, without listing them in the geometry_columns table. 
  
 Regards, 
 Wouter

10

Wouter, 
  
 Thanks for your sharing, your words reminds me about the schema supporting a couple of weeks ago, yes, that absolutely an awesome feature. Rose, just have a try on this feature. 
  
 Thanks. 
  
 Yale 


11

Hi, 
  
 Unfortunately, Rose is right. I ran into the same issue, while checking out something else. It doesn’t really hurt, but potentially slows down table-lookups, as all the geographic information has to be put through twice. 
 In my case it revealed something else which is a little bit sad: when requesting single values for a certain column using the querytools, you have to retrieve all columns, or maybe you should assemble the collection of columns that you want to retrieve manually, which is a cumbersome process, way to difficult. Choosing which columns to retrieve should be based on a simple collection of strings. 
 In my case, I would probably write all SQL from sratch myself, when retrieving specific single pieces of information (single value lookup, or single rows), which is not too hard. Something like this: 
  
 'Build the query string (contains a local function to get the map X and Y coordinates with a little less code) 
 'Also insert the columnname and the SRID. As you can see, it’s not even neccessary to retrieve the geometry data or keyvalues! 
 Dim sSQL As String 
 sSQL = String.Format("select {0} from nl.vw_adm_gem_2008 " & _ 
             “where GeomFromText(‘POINT({1} {2})’,{3}) && the_geom;”, _ 
             Chr(34) & “GEMNM” & Chr(34), CType(Me.GetGeoPt(e.X, e.Y).X, Int32), CType(Me.GetGeoPt(e.X, e.Y).Y, Int32), 28992) 
  
 'finally perform a ExecuteScalar. 
 TextBox1.AppendText(postcode.QueryTools.ExecuteScalar(sSQL)) 
  
 This is really fast for single value lookups, for entire rows you’ll need ExecuteQuery. Maybe I’ll even get around it completely, and use only the featureid and get all tabledata in another way. As a matter of fact, it would be better to use prepared statements and paramaters instead of putting together the SQL this way (mainly because of protection against SQL-injection), but if your applications manages it well, this can be safe too. 
  
 As said before, retrieving too many columns, or even twice, does not hurt, but when  many people are using the same database, or when working with large tables, it could become important that MapSuite optimizes it queries. 
  
 So, this is definitely a point for improvements in upcoming releases. 
  
 Regards, 
 Wouter 


12

Posted By Yale on 11-23-2009 07:39 PM 

Rose,


Sorry for making this mistake.
 
I tested with our very simple sample, it works fine, it will only regrieve the geometry column once, could you explain what special operation for you case?
 
select AsBinary("shape") as "shape",recid from "usstates" where GeomFromWKB(:geometry,4326) && "shape" ORDER BY COLUMN1 DESC;
 
Any more questions just feel free to let me know.
 
Thanks.
 
Yale


Yale:


I was distracted to another project in the past few day.


I did not use any special operation, there is only one Public schema in my database,  first I created a view, then created the PostgreSQL feature layer on this view. I can see there is only one geometry colum in the view.



Thanks


 


Rose



13

Wouter & Rose, 
  
 Thanks for your sharing and posts, I appreciate it very much. 
  
 I have added this issue to work working track system, hope we can optimize it in future. 
  
 Any more questions just feel free to let me know. 
  
 Thanks. 
  
 Yale