massive number of points and polygon area

Hi,

 

 

In this case, since the number of polygons is not so many, I would try the PointOnAreaOverlayer transformer.

 

 

First, add a reader to read the 30k+ polygons from the MapInfo files.

 

Second, add a reader to read the 15+ million points from the CSV file. Create point geometries through CSV reader parameter setting or the VertexCreator. And, it's better that you remove unnecessary attributes from the point features.

 

 

Make sure that the MapInfo reader is on the top most in the Navigator, so that the polygons will be read first. It's very important.

 

 

Add a PointOnAreaOverlayer transformer,

 

- send the polygons to the Area port,

 

- send the points to the Point port,

 

- set "Yes" to the Area First parameter, to avoid consuming huge memory for caching the points.

 

 

Then, every polygon output from the Area port will have "_overlaps" attribute which stores the number of inside points.

 

 

Takashi

Both CSV and Mapinfo Tab don't support any spatial querying mechanism, so you cannot avoid reading the 15+ million points at least once. But just once is enough. FME performs spatial indexing internally when testing spatial relationships is needed.

 

Therefore, I think the PointOnAreaOverlayer would be one of the most efficient solutions in this case, if you read the polygons first and also set "Yes" to the "Area First" parameter.

Hi ETLS-Itay/Takashi,

 

 

could you elaborate more on the "bounding box" part?

 

 

so what I did is to convert the +15million points to an FFS file. (it was split into 6 files - 5files (.ffs) and 1 (.fsi)). it finished successfuly. (with spatial index). [took about ~20mins to run successfully]

 

 

thus my input on my area overlay would be the FFS file(which contains 15million points) and 

 

the 30k++ polygons which serves as input on my area.

 

 

these would be my input on the PointOnAreaOverlay

 

 

 

 

I tried running only few sample polygon files (30 only) but it took forever to run..

 

 

I tried looking for a bounding box transformer but can only find the following:

 

1. BoundingBoxAccumulator

 

2. BoundingBoxReplacer

 

but doesnt make sense to me at the moment.

 

 

I also included Takashi's recommendation to tick "Area First" to "yes" and it reads the input initially which works fine.

 

 

would appreciate your expertise on this... thanks in advance.

 

 

kind regards,

 

Jubert

 

 

Have you tried spatial database + FeatureReader?

If you need to write attributes of every point corresponding to a polygon into the csv file, you should use the point features which will be output from the <Generic> port of the FeatureReader.

 

 

In this case, I think you will have to merge attributes of the polygon feature (Initiator) to the point features, because you are using a polygon attribute as the destination csv file name. You can control it with parameters in the "Advanced > Attribute and Geometry Handling" section of the FeatureReader parameters dialog.

 

And, note that there could be confliction of attribute names between polygon and point. If there were confliction, you may have to resolve them appropriately depending on your requirement.

That's too slow. 30k polygons cannot be completed forever...

 

 

Another approach that uses database.

 

?I expect that database is much faster than ffs.

 

 

Preparing:

 

Save the points into a PostGIS database table (e.g. "point_table").

 

 

Workspace:

 

(1) Read the polygons with a MapInfo reader.

 

(2) Extract the polygon geometry with the GeometryExtractor as OGC Well Known Text and store it to an attribute (e.g. "_geometry").

 

(3) Add a SQLExecutor to query against the "point_table" to fetch the records of the points which are inside of the polygon. The SQL statement looks like this.

 

-----

 

select attr1, attr2, attr3,

 

'@Value(fme_basename)' as dest_csv_name

 

from point_table

 

where ST_Contains('@Value(_geometry)'::geometry, geom)

 

-----

 

 

attr1, attr2, ... indicate the attributes of the points. You can replace them with the actual field names. Select minimal desired attributes here to avoid geometry creation and increase performance.

 

 

"dest_csv_name" stores the fme_basename value of the Initiator polygon. It can be used as the destination CSV file name in the CSV wirter feature type. If you need other polygon attributes, you can add them by the same way.

 

 

The where clause performs the spatial filtering using PostGIS function. See here to learn more about the function.

 

ST_Contains (http://postgis.net/docs/ST_Contains.html)

 

 

And then, expose the attribute names manually with the "Attributes to Expose" parameter.

 

You can set "Result Attributes Only" to the "Combine Attributes" parameter, because required polygon attributes (i.e. fme_basename and others) have been merged already by the SQL statement.

Hi TAkashi, yes it is slow..i think it consumes most time in writing the csv file. I will try to run a higher number of sample polygons over the weekend and i will try the PostGIS on monday method. hopefully it solves the problem. thanks so much for your help. really appreciate it.

 

 

kind regards,

 

Jubert 

I would Insert these records in some kind of Spatial database (if possible). You could use Oracle, SQL Server or PostGis for that. And after doing so I would use a SQL statement to calculate the join in the database.